aggregate rank function with previous month data comparison

[#1]

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!

[#2]

Would you mind attaching a example .qvw file?

[#3]

Hi Eugene,

please find attached an example file with example data.

[#4 · Accepted solution]

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.

[#5]

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.