Qlik Community

Qlik Sense Cloud Discussions

rgreben
New Contributor III

To 5 Not Using a Variable

 

Hello Group!

I would like to show Top 5 accounts based on Sales $ Growth.  I have two Sales $ Growth measures, one that calculates Sales $ Growth for YTD (i.e. [January + February + partial March 2019] - [January + February + partial March 2018]) and another that calculates Sales $ Growth for full months in the current year (i.e. [January +February 2019] - [January +February 2018])

I have the formula for Sales $ Growth for YTD already which a member of this grouped helped me with: Aggr(if(rank($(vNetSalesYTDGrowth$), 4,1) < 6, [Individual Customer Name]), [Individual Customer Name]).

I need help with the formula for Sales $ Growth for full months in the current year. This is how Sales $ Growth for full months in the current year is defined:

Num(sum({$<[Fiscal Month]=,[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal MonthYear]=,[Calendar.Date]=,
[Fiscal Year]={"$(=max([Fiscal Year]))"},
[Fiscal Month Number] = {"<$(=max({<[Fiscal Year]={$(=max([Fiscal Year]))}>} [Fiscal Month Number]))"}
//[Nth Day of Fiscal Quarter]={"=$(=maxstring([Fiscal QuarterYear])) [Nth Day of Fiscal Quarter]))"}
>}NetSales)-
sum({<[Fiscal Month]=,[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal MonthYear]=,[Calendar.Date]=,
[Fiscal Year]={"$(=max([Fiscal Year])-1)"},
[Fiscal Month Number] = {"<$(=max({<[Fiscal Year]={$(=max([Fiscal Year]))}>} [Fiscal Month Number]))"}
>}NetSales),'$#,##0;($#,##0)')

I am not in a position to create new variables, so this lengthy expression is what I have to work with. How should the Aggr(if(rank( formula above be changed?

Thank you.