Complex Rank/Aggr/Set Analysis

Thu, 13 Jun 2013 13:47:41 GMT

Hi Folks

I have what appears to be a fairly simple requirement to show the Top N Winners & Top N Losers in a data set, the definition of winners is two-fold:

1. They have to have total revenues of > specific variable

2. They have to have YoY growth of > specific variable

Top N is further complicated by the fact that I can base the ranking on any of four columns.

I think then that I need to use some complex set analysis to define the list of clients that come within the realms of a group of winners, and to wrap an Aggr & Rank function around that to calculate the rank.

So, if I start with this:

Sum({$<[TradeDateNum]={">=$(vStartDate)<=$(vEndDate)"}>}Revenues)

That is my basic formula for the calculation of revenues, to add the Aggr/Rank to this I do:

Aggr(Rank(Sum({$<[TradeDateNum]={">=$(vStartDate)<=$(vEndDate)"}>}Revenues),1,1),[ClientName])

All of the above works fine, but now what I need to do is to only include those clients where the total revenues is greater than the value of variable vWinnerVal and where the YoY Growth is greater than vWinnerPct.

Revenues in the above case would be calculated as per first formula above, YoY Growth is calculated as:

( Sum({$<[TradeDateNum]={">=$(vStartDate)<=$(vEndDate)"}>}Revenues) - Sum({$<[TradeDateNum]={">=$(vStartDatePrior)<=$(vEndDatePrior)"}>}Revenues) ) / FABS( Sum({$<[TradeDateNum]={">=$(vStartDatePrior)<=$(vEndDatePrior)"}>}Revenues) )

To make a little simpler in terms of the amount of typing!! the above are stored in variables, so I actually create expressions such as:

For Revenues: $(vCurrentPeriodRevenues)

For YoY Growth: ( $(vCurrentPeriodRevenues) - $(vPriorPeriodRevenues) ) / FABS ( $(vPriorPeriodRevenues) )

If anybody can help out with this, I'd really appreciate it.

topic Complex Rank/Aggr/Set Analysis in QlikView

Complex Rank/Aggr/Set Analysis