Pivot Figures Download
Othon Sdcd
The issue is one field is pulling through incorrect data or not calculating. In the images below, the "Allocated %age" is a Master measure using variables, the "Allocated %age(1)" is a measure using the expressions used to create the Master Measure and only placed there to test if the Master Measure was the issue or the expressions.
The two screenshots have different data. For example C01A in the first has a value of 254 instead in the second it is 293. It means they are generated with different filters. Apply the same filters of Qlik NPrinting in the Qlik Sense app, check that the first column has the same data in both cases and then verify the percentages.
It could be useful to add the formula =getcurrentselections() in the template so you can verify which filters were applied in the generated reports and you can easily compare them with the ones applied in the Qlik Sense app.
I posted the wrong images, I had several versions open when writing my first post. The data displayed is irrelevant really, it's the NPrint Allocated %age that always returns 100% (or 1 depending on formatting) for every version/test that I run and Qlik Sense returns that actual Allocated %age.
The spreadsheet is generated by importing the full pivot table and a filter is applied at table level, but I can't see why all other fields change and match Qlik Sense but the NPrint Allocated %age doesn't
On my main data sheet I have a column labled Supervisor Name and another labled Department. In this main sheet I calculate numbers that correspond with an employee name for each month. I ran a pivot table and sorted it by department and I take the sum of each month's numbers which relate to an employee name. The pivot table is great, it gives me almost everything that I need but I would like to create another column on the pivot table and create a % of a total sum, add Supervisor names and departments associated with the employees on the pivot table. When I add columns to the pivot table, my pivot table does not update. I was unsure how to do a vlookup for Supervisor name and department so I manually did the vlookup in an Excel file and copy and pasted this data into the pivot table. What is the best way for capturing data from the main data sheet and also from the pivot table? I want to run reports off of the pivot table with cumulative total data to then run dynamic views. I update the main data sheet monthly.
For the "range" of the INDEX formula, you'll need to create a sheet reference to your main sheet and select the column header for "Supervisor Name", making sure that the whole column is selected. -create-cross-sheet-references
To your point of "pivot table does not update" please double check your "Execution Frequency" in your pivot settings. The recommended setting is at least an hour or longer. We've noticed that "immediately" doesn't happen instantaneously, but rather gets added to a queue so it may take some time. -app-not-automatically-updating
I'm trying include a date range with counting the number of applicants within various depts, in certain date ranges, but it's saying incorrect argument set. =COUNTIFS(DISTINCT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], AND(@cell > DATE (2023, 9, 30), @cell
Hi there. I've asked a few times about pivot tables (and written my own js from scratch). However I think a really good interim, possibly permanent solution would be to integrate react-pivottable as a custom component.
However, I've tried, but (a) my development chops are not up to it and (b) I find the custom component help confusing, it's not really clear on what syntax you should use to pass data from the model to component.
Hi Victoria. This is great news. When will the Pivot table function be available? I was able to implement domjammoo's well laid out use of pivottable.js which is great for display but I need the capability of allowing users to edit certain data points in the table. Hoping this feature is right around the corner.
Yes, you can implement your own with a lot of dev work, but all of your competitors have a pivot table available and it makes it very tempting to jump ship. My org is considering it, as without a pivot table real data exploration is being limited in our org.
@Victoria I was able to get a pivot table to display but used Oracle to do it. The next thing after a Pivot Table is available is the ability to then programmatically change editability by column. Of course with the pivot table I now have a set of columns that dynamically change based on the data set. I want to be able to allow the user to edit that data but only based on a prefix of the column. It looks like there is JS to do this but it doesn't work
Personally i think there are two things here - editing data in a pivot table can be problematic as often the value in each intersect is an aggregated figure (e.g. SUM) - if the user pivots the table to show subtotals, then it does not make sense to edit them as it cannot be translated to the underlying data.
One idea I had @victoria - many of my use cases are of the second type and all I really want is the columns to represent rows, and the rows to represent values - so for instance you can have a table with one column per customer, where the first row is customer name, and all of their details below.
My use case is simply trying to plan out hours by person by project by month. Displaying the data as you would store it in a database is NOT visually user friendly. This is where that crosstab functionality would do the trick. I was dynamically pivoting the table in Oracle and displaying it in a Retool table which visually works but you can't edit it because the columns are dynamic. You can't programmatically set the column as editable in Retool so it's display only. I dunno, every way I spin this I run into some roadblock and the majority of them are in Retool... #frustrating.
Yeah I'm not sure it's just a retool problem. Every javascript component library has a table component, but none of them allow you to use a data attribute as a dynamic column, and a data attribute for the rows. Effectively it's a table with dynamic values on both the x and y axes:
Anyway - I had an idea - given the that time periods are by definition static, I just did a prototype of an approach which is definitely going to solve some of my use case problems - interested to know if it solves your issue:
Without a way to pivot the data dynamically in the DB, this becomes an absolute nightmare if the columns need to be dynamic. The workarounds are convoluted and difficult to follow; this should really be prioritized, either through enabling something in the Retool DB or (preferably) through the UI.
I have managed to enter a data table into Spotfire (version 6.0) and create a pivot from the data table. However I am unable to make it such that when I filter the original data, the pivot reacts to the filter and gets recalculated accordingly.
So from this, how can I make the values of the pivot table automatically adjust when I change the filter of the original table. So for example if I change the age filter to above 30 then it will change to:
I took your data set and pasted it into Spotfire and achieved what you described using a Cross Table visualization. The image below should help you see what to do. Notice that I filtered the age column for 31-62 and the results match the figures in the third data sample you provided.
Looks like this depends heavily on your version of Excel. I am using the 2007 version and it offers no wizard option when you right click on the table. You need to click on the pivot table to make extra 'PivotTable Tools' appear to the right of the other tabs at the top of the screen. Click the 'options' tab that appears here then there is a big icon on the middle of the ribbon named 'change data source'.
To get the right range and avoid an error message... select the contents of the existing field and delete it, then switch to the new data source worksheet and highlight the data area (the dialog box will stay on top of all windows). Once you've selected the new data source correctly, it will fill in the blank field (which you deleted before) in the dialog box. Click OK. Switch back to your pivot table and it should have updated with the new data from the new source.
Be a bit wary of any solution that doesn't involve re-creating the PivotTable from scratch. It is possible for your pivot fields' option names to get out of sync with the values they present to the database.
For example, in one workbook I'm dealing with involving demographic data, if you try to select the "20-24" age band option, Excel actually presents you with the figures for ages 25-29. It doesn't tell you it's doing this, of course.
Hello,
Cannot quite grasp what you are wanting to do that is not there already. What about the drop downs on the chart itself?
.
Pivot table:
.
have selections on chart itself for File and Month Raised:
@DPSSpatial
As I am certainly not proficient with pivot tables, there is probably an easier method. Did a minor re-arrangement of data on sheet3 then new chart. What you wanted? Please note in messing around may have changed original pivot table and chart.
Stanley,
It simply means your data is based on those category combinations that is
why you are seeing them. Please share a print screen of that data set
whose data elements you pivoting for now.
Maybe my set up is different. I have data elements assigned into a data set with category combination. My data elements are using the default category combo -after I was unable to assign categories combo to the data elements.
I posted some data into the data elements while following the section on: sending datavalues in the dhis2 developers guide. My API client returns a status code: 200, meaning that data was successfully sent.
c80f0f1006