Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikerz,
I have the following requirement.
I have a Pivot table like below:
Dimension Measure 1 Measure 2 Measure 3 Measure 4
Sector | # Headcount ( PY ) | # Headcount ( CY ) | # Change | % Change |
Sector 1 | 6,637 | 7,296 | 659 | 9.9% |
Sector 2 | 2,786 | 3,105 | 319 | 11.5% |
Sector 3 | 1,666 | 2,045 | 379 | 22.7% |
Sector 4 | 951 | 893 | -58 | -6.1% |
Sector 5 | 751 | 771 | 20 | 2.7% |
Sector 6 | 12 | 17 | 5 | 41.7% |
Sector 7 | 459 | 0 | -459 | -100.0% |
# Change is nothing but the diff in CY(Current Year) and PY(Previous Year) headcount and % Change is the percent in # Change.
And I have a Range selection of (Change # - Measure 3) like below (only one selected at a time):
> 100
> 75,
> 50,
> 25,
> 10,
> 0,
< 0,
< -10,
< -25,
< -50,
< -75,
< -100
So based on the selection of Range(Change # - Measure 3) , the Pivot table should change.
For example, If I select ">0", the following data should be populated in the pivot table(i.e. # Change > 0).
Sector | # Headcount ( 2017 ) | # Headcount ( 2018 ) | # Change | % Change |
Sector 1 | 6,637 | 7,296 | 659 | 9.9% |
Sector 2 | 2,786 | 3,105 | 319 | 11.5% |
Sector 3 | 1,666 | 2,045 | 379 | 22.7% |
Sector 5 | 751 | 771 | 20 | 2.7% |
Sector 6 | 12 | 17 | 5 | 41.7% |
I am able to achieve this, if I have only one measure i.e. # Change - Measure 3 in the pivot table, like below,
Sector | # Change |
Sector 1 | 659 |
Sector 2 | 319 |
Sector 3 | 379 |
Sector 5 | 20 |
Sector 6 | 5 |
but when I include other measures and apply the same logical condition used in Measure 3 to other Measures, it does not work on other measures.
I get the following output when used with multiple measures.
Sector | # Headcount ( 2017 ) | # Headcount ( 2018 ) | # Change | % Change |
Sector 1 | 6,637 | 7,296 | 659 | 9.9% |
Sector 2 | 2,786 | 3,105 | 319 | 11.5% |
Sector 3 | 1,666 | 2,045 | 379 | 22.7% |
Sector 4 | 951 | 893 | - | -6.1% |
Sector 5 | 751 | 771 | 20 | 2.7% |
Sector 6 | 12 | 17 | 5 | 41.7% |
Sector 7 | 459 | 0 | - | -100.0% |
Which is not expected. The highlighted rows should not be populated.
Please let me if there is any work around for this.
Hope I was able to elaborate my requirement prominently, Thanks in Advance for you help.
hi,
Please tell me that whether the highlighted part occur even you select the >0 filter?
Thanks....
Yes Priyal, it occurs.
Use if condtion for filter.
If(((headcount CY-headcount PY)/headcount CY)>0,'>0', And so on...
Thanks...
Priyal
Priyal,
Thanks for the reply.
I don't want to populate range values, I already have them in a filter.
My requirement is to populate values in the pivot table based on the selection in the filter.
Thanks.