4 Replies Latest reply: Aug 22, 2018 6:04 AM by Ganesh S

# Range Selection in Qlik Sense Pivot table

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.

• ###### Re: Range Selection in Qlik Sense Pivot table

hi,

Please tell me that whether the highlighted part occur even you select the >0 filter?

Thanks....

• ###### Re: Range Selection in Qlik Sense Pivot table

Yes Priyal, it occurs.

• ###### Re: Range Selection in Qlik Sense Pivot table

Use if condtion for filter.

Thanks...

Priyal

• ###### Re: Range Selection in Qlik Sense Pivot table

Priyal,