Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Display zero values but do NOT rank them

Hi, I have a requirement to display all values in pivot chart but rank ONLY non-zero values. Below is the base data with expected Rank in last column: (If 2015 or 2016 revenue is zero, do NOT Rank them)

Expected Result:

   

EMP_ID2015 REVENUE2016 REVENUEGROWTH (2016-2015)GROWTH RANK
A11002001001
A30100100
A530050-2504
A6500-50
A75075252
A810025-753

If I give ROWTH RANK = IF ([2015 REVENUE] <> 0 AND [2016 REVENUE] <> 0, Rank([GROWTH],0,1), ' ' ) I am getting below result which is not I need.

   

EMP_ID2015 REVENUE2016 REVENUEGROWTH (2016-2015)GROWTH RANK
A11002001001
A30100100
A530050-2506
A6500-50
A75075253
A810025-755
Tags (1)
7 Replies

Re: Display zero values but do NOT rank them

Try this:

Rank(If([2015 REVENUE] <> 0 and [2016 REVENUE] <> 0, [GROWTH]), 0, 1)

Not applicable

Re: Display zero values but do NOT rank them

Didn't work. It gave ranks as 1,1,6,4,3,5. I am expecting 1,space,4,space,2,3

Re: Display zero values but do NOT rank them

Would you be able to share a sample?

Not applicable

Re: Display zero values but do NOT rank them

@ Sunny, Attached the test qvw for reference.

Re: Display zero values but do NOT rank them

I think the field names were not right. You used _ in your field name which was missing

=Rank(If([2015_REVENUE] <> 0 and [2016_REVENUE] <> 0, [GROWTH]), 0, 1)

Capture.PNG

jayanttibhe
Contributor III

Re: Display zero values but do NOT rank them

OR Get rid off these If condition and Use Pick / Match :

Rank(Pick(-1*(not match(RangeSum([2015_REVENUE]+[2016_REVENUE]),[2015_REVENUE],[2016_REVENUE])),[GROWTH]), 0, 1)

Re: Display zero values but do NOT rank them

Might be better performing (not sure)... but looks way more complicated.... def. give it a shot though