topic Re: How to fix the rank irrespective of selection in QlikView

How to fix the rank irrespective of selection

farheenayesha — Sat, 16 Nov 2024 03:31:06 GMT

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

Re: How to fix the rank irrespective of selection

sunny_talwar — Tue, 21 May 2019 13:27:48 GMT

Which tab is this chart on?

Re: How to fix the rank irrespective of selection

farheenayesha — Tue, 21 May 2019 13:51:40 GMT

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

Re: How to fix the rank irrespective of selection

sunny_talwar — Tue, 21 May 2019 13:53:54 GMT

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

Re: How to fix the rank irrespective of selection

farheenayesha — Tue, 21 May 2019 13:56:59 GMT

Sorry. Attaching the correct file.

Re: How to fix the rank irrespective of selection

sunny_talwar — Tue, 21 May 2019 14:00:20 GMT

Try this

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

Re: How to fix the rank irrespective of selection

farheenayesha — Tue, 21 May 2019 14:06:09 GMT

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?

Re: How to fix the rank irrespective of selection

sunny_talwar — Tue, 21 May 2019 14:20:51 GMT

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

Re: How to fix the rank irrespective of selection

farheenayesha — Tue, 21 May 2019 14:26:54 GMT

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/1581660#M442950

Re: How to fix the rank irrespective of selection

sunny_talwar — Tue, 21 May 2019 14:32:25 GMT

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

Re: How to fix the rank irrespective of selection

farheenayesha — Wed, 22 May 2019 04:40:10 GMT

Yes i tried. But i was not so clear so didn't get the result.

Re: How to fix the rank irrespective of selection

farheenayesha — Fri, 24 May 2019 08:38:25 GMT

Hi Sunny,

When i am using both National and regional ranking together, it is giving incorrect regional ranking. Please find the snapshot below. Regional ranking should be from 1 to 7.

Please help me to get the correct regional ranking along with national.

Re: How to fix the rank irrespective of selection

sunny_talwar — Fri, 24 May 2019 11:09:16 GMT

Help how? Please attach a sample or provide more details 🙂

Re: How to fix the rank irrespective of selection

farheenayesha — Mon, 27 May 2019 09:04:14 GMT

Hi sunny,

Please find the qvf attached. Please refer sheet 'Division'.

Re: How to fix the rank irrespective of selection

farheenayesha — Thu, 30 May 2019 04:43:26 GMT

Hi Sunny,

Attached sample data and qvf. Please help.

Re: How to fix the rank irrespective of selection

farheenayesha — Thu, 30 May 2019 04:45:25 GMT

Hi Sunny,

Attached sample data as well. Please help.

Re: How to fix the rank irrespective of selection

farheenayesha — Thu, 30 May 2019 04:45:30 GMT

Hi Sunny,

Attached sample data as well. Please help.

Re: How to fix the rank irrespective of selection

sunny_talwar — Thu, 30 May 2019 11:47:35 GMT

May be this

Aggr(Rank(Sum(Sales)-Sum(Base)), COMM_CAT, DIV_NM) * Avg(1)

Re: How to fix the rank irrespective of selection

farheenayesha — Thu, 30 May 2019 14:05:04 GMT

Perfect !! You are great. Thank you so much Sunny.