topic aggregate rank function with previous month data comparison in QlikView

aggregate rank function with previous month data comparison

Tue, 09 Jun 2009 21:32:08 GMT

Hi,

i tried to make a dynamic dimension which should show the top / worse 5 of products.
The value which should be calculated is the difference between current month value and foremonth value.

For me it is necessary to calculate the difference on which the rank should be calculated.

=aggr(if(rank(
sum(if (inmonth(makedate(jahr,monat),AktQuart,0),trn_externallimit))
-
sum( if (inmonth(makedate(jahr,monat),AktQuart,-1),trn_externallimit))
) < 6 , trn_product_name,'Others') ,trn_product_name)

This formula does not work correctly. The ranks are not correctly calculated. If I use only one "Sum" formula inside the rank function it work correct.

Is there a possibility to get this formula working or should it be better to calculate the difference inside the script to use the rank formula on the new column?

Thanks in advance!

aggregate rank function with previous month data comparison

Wed, 10 Jun 2009 00:25:14 GMT

Would you mind attaching a example .qvw file?

aggregate rank function with previous month data comparison

Wed, 10 Jun 2009 15:13:07 GMT

Hi Eugene,

please find attached an example file with example data.

aggregate rank function with previous month data comparison

Thu, 11 Jun 2009 03:53:59 GMT

The problem you are having is that you have three items with the same rank, as can be seen below and in attached file. You must find a way to introduce more variation into your rank differentiation expression.

aggregate rank function with previous month data comparison

Mon, 15 Jun 2009 16:32:28 GMT

Hi Eugene,

thank you very much for your answer. I think this will explain the problem it and I have to find a solution.

Many thanks.