Discussion board where members can get started with QlikView.
I have a pivot table.
but I am not able to Sort in that pivot table.
My Pivot table like below.
And sort tab of my pivot table like below
I have like this but Sorting is not worked in my pivot table.
Is it possible to sort in the pivot table?
I have three dimension , that's why I have a problem?
please help me.
Thanks in advance....
Sort expression columns in a pivot table
1. Create variables for the expressions you want to sort by
LET vEqProjectBaseline = 'Sum([BackingSheetHours]) /7.5'; LET vEqEstimate = 'Sum(Aggr([Estimated Hours], ProjectTaskKey)) /7.5'; LET vEqBaselineVsEstimate = '($(vEqEstimate)) / ($(vEqProjectBaseline))';
N.B – brackets and apostrophes are a real minefield
2. Create function to evaluate the variables inline
SET fxEval = Evaluate($1);
3. Create the custom sort table:
CustomSorting: LOAD %DisplayValue ,%ExVariable ,$(fxEval(%ExVariable)) as %SortingExpression Inline [ %DisplayValue ,%ExVariable Project Baseline ,vEqProjectBaseline Estimate ,vEqEstimate Baseline vs Estimate ,vEqBaselineVsEstimate
4. Set the sort expression in the pivot table:
5. Create a selector for the sorting options (I used %DisplayValue in this example)
Adds sorting to pivot tables
Doesn’t seem to affect performance (not yet tested on large datasets)
This will probably be a bummer to maintain (– I’m thinking mismatching expressions between the table and the variables)
Takes a couple of hours to set up and test
You'll only be able to sort ASC [OR] DESC, not have the option of both
Hope that helps!