topic Re: How to find gap using ranks in QlikView

How to find gap using ranks

Sat, 16 Aug 2014 22:42:26 GMT

Hi,

I have a requirement to show revenue gap using revenue and cost ranks.. In below example, client h needs to make additional $570.9695744 to reach to no. 1 revenue rank position... formula is - 983.3415781 - 412.3720037 = 570.9695744

if a client's revenue rank is already better than its cost rank, then we don't need to calculate revenue gap..

Looking for any advise to build this type of chart... i don't want to pre calculate as the calculations need to happen at run time based on selected filters...

appreciate any suggestions... thanks!

Re: How to find gap using ranks

MK_QSL — Sun, 17 Aug 2014 06:21:00 GMT

IF(rank(sum(revenue),1,1) > rank(sum(cost),1,1), Max(TOTAL Aggr(SUM(revenue),client))-SUM(revenue),NULL())

Re: How to find gap using ranks

Mon, 18 Aug 2014 13:38:31 GMT

Manish, thanks for replying..

I should have given more description on the requirement... the solution you have given will work only for client that has highest cost rank.

If you look at the 2nd example, it needs to find 2nd placed client with most revenue and find out how much more $ to make to reach to 2nd position... so in this case, formula would be - 817.7175449 - 781.8435362=35.8740087... in 3rd example, comparison should be with 3rd placed client based on revenue.

Basically, the idea is to check client's cost rank and compare against revenue rank... if client is consuming more costs than the revenue it is generating, then we need to find out how much more $ that client should make to reach to the revenue rank which will be similar to its cost rank...

Re: How to find gap using ranks

MK_QSL — Mon, 18 Aug 2014 13:59:39 GMT

Sorry but dont able to catch your requirements.... Can you provide solution in excel file with little more explanation?

Re: How to find gap using ranks

Mon, 18 Aug 2014 14:13:04 GMT

Attached is the excel version... column F has the formula that shows how the gap is getting calculated...

Re: How to find gap using ranks

Tue, 19 Aug 2014 09:56:37 GMT

Bump!

appreciate any advice...

Re: How to find gap using ranks

Gysbert_Wassenaar — Fri, 22 Aug 2014 13:50:46 GMT

See attached qvw.

Re: How to find gap using ranks

Fri, 22 Aug 2014 16:50:46 GMT

Gysbert, thanks for the reply..

I am actually looking for a dynamic solution... dont want to precalculate the implied revenue gap as that should be recalculated based on some filters.. the example that I uploaded was for illustration purpose of the logic that I am looking for..

appreciate if you could provide any suggestions for dynamically calculating such measure at runtime..

again, thanks for taking time to look into this issue..

Re: How to find gap using ranks

Gysbert_Wassenaar — Sat, 23 Aug 2014 18:53:50 GMT

Look again.

This time look at the chart expressions instead of at the script.

Re: How to find gap using ranks

Sat, 23 Aug 2014 19:42:21 GMT

I should have been explicit! The solution works but the expression have hardcoding for 10 clients... my actual client list is in thousands... and filter selections changes the list of applicable clients dynamically...

correct me If I am missing something that would help me to remove below hardcoding... i liked this idea, but not able to build an expression that would dynamically pick the revenue amount based on cost rank...

if(rank(sum(revenue))>rank(sum(cost)),

rangesum(

pick(rank(sum(cost)),

$(vMax(1)),

$(vMax(2)),

$(vMax(3)),

$(vMax(4)),

$(vMax(5)),

$(vMax(6)),

$(vMax(7)),

$(vMax(8)),

$(vMax(9)),

$(vMax(10))

-sum(revenue)

)

Re: How to find gap using ranks

Gysbert_Wassenaar — Mon, 25 Aug 2014 09:24:26 GMT

You can generate that too if you like:

if(rank(sum(revenue))>rank(sum(cost)),

rangesum(

pick(rank(sum(cost)),

$(=concat( 'max(total aggr(sum(revenue),client),' &

num(ValueLoop(1,GetPossibleCount(client)),'00') & ')' &chr(10) ,',' ))

-sum(revenue)

)

Re: How to find gap using ranks

Tue, 26 Aug 2014 02:26:50 GMT

thanks Gysbert! the solution works fine!

my only concern is performance... for more then 1000 clients, it takes a while to calculate... am thinking of adding a condition to show chart data only when applicable list of clients is below 1000...

thanks again... really appreciate your help with this... !