Range Selection in Qlik Sense Pivot table

[ganeshsvm]

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.

[priyalvp24]

hi,

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

Thanks....

[ganeshsvm]

Yes Priyal, it occurs.

[priyalvp24]

Use if condtion for filter.

If(((headcount CY-headcount PY)/headcount CY)>0,'>0', And so on...

Thanks...

Priyal

[ganeshsvm]

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.