How to fix the rank irrespective of selection

[farheenayesha]

Hi,

I want to fix the rank irrespective of selection. In the below snapshot, Division 13 has Rank as 14. However, when i select the region in the filter, its rank changes to 7. Please help me to fix the rank. Attaching the dummy data and app.

@sunny_talwar 

[sunny_talwar]

Which tab is this chart on?

[farheenayesha]

Hi Sunny,
Its in Division tab. Please help me to fix the Rank.

[sunny_talwar]

I am seeing this from the attached app... how do I get the numbers?

[farheenayesha]

Sorry. Attaching the correct file. 

[sunny_talwar · Accepted solution]

Try this

Rank(Sum({<REG_NM>}Sales)-Sum({<REG_NM>}Base)) * Avg(1)

[farheenayesha]

You are genius Sunny. As usual you solved my biggest problem. Thank you so much.
One last question, Please let me know how does Avg(1) works?

[sunny_talwar]

This is just to remove rows which are not selected.... Avg(1) = 1 for selection and Avg(1) = 0 for non selection

[farheenayesha]

Thank you so much sunny. Would need your help in the below post as well. Please help.

https://community.qlik.com/t5/QlikView-App-Development/How-to-convert-missing-null-value-to-zero/m-p...

[sunny_talwar]

What @dplr-rn has mentioned might be the only way to do this. Have you tried that?