Conditional Rank with Aggr

[cmenszak1]

I am trying to compare current year sales (vCY_Year) to prior year sales (vPY_Sales) and want to rank in descending order the Corporate Accounts with the largest difference between the years, but only rank the Corporate Accounts with <=0 total sales in the current year.   I can't figure out how rank only the ones with <=0 total sales with rank and aggr.  This expression includes the accounts with positive sales for the current year too.

sum({1}aggr ( Rank(-
(sum({$<[Year]={"$(=vCY_Year)"}>}[Sales USD])
-sum({$<[Year]={"$(=vPY_Year)"}>}[Sales USD])))
,[Corporate Account]))

[JGMDataAnalysis]

Try with this...

Rank(
Sum({< [Year] = {"$(=vPY_Year)"}, [Corporate Account] = {"=Sum({<[Year] = {'$(=vCY_Year)'}>} [Sales USD]) <= 0"}>} [Sales USD]),
-Sum({<[Year] = {"$(=vCY_Year)"}, [Corporate Account] = {"=Sum({<[Year] = {'$(=vCY_Year)'}>} [Sales USD]) <= 0"}>} [Sales USD])
)

[qliksus]

sum({1}aggr ( Rank(-
(sum({$<[Year]={"$(=vCY_Year)"}>}[Sales USD])
-sum({$<[Year]={"$(=vPY_Year)"} , [Corporate Account]={"=sum({$<[Year]={"$(=vPY_Year)"}>}[Sales USD])<=0"} >}[Sales USD])))
,[Corporate Account]))

[cmenszak1]

I tried the revised expression and it doesn't quite work, the rows with zero sales in the current year are not being ranked.   I only want to rank the ones with zero (or less than zero) sales in the current year (vCY_Year).  I attached a very simple sample app.

[sunny_talwar]

Try this

If(Sum({$<[Year]={[$(=vCY_Year)]}>}[Sales USD]) + Sum({1} 0) <= 0,
Rank(Sum({$<[Year]={"$(=vPY_Year)"}, [Corporate Account] = {"=Sum({$<[Year]={[$(=vCY_Year)]}>}[Sales USD]) + Sum({1} 0) <= 0"}>}[Sales USD])-Sum({$<[Year]={"$(=vCY_Year)"}, [Corporate Account] = {"=Sum({$<[Year]={[$(=vCY_Year)]}>}[Sales USD]) + Sum({1} 0) <= 0"}>}[Sales USD])),
0)

[cmenszak1]

Thanks for the help.  That expression does what I need when used in a table. 

Ultimately what I need to accomplish is a text box with the customer ranked number one (based on largest YoY decline and with current year sales <=0).  I am assuming I need to use the AGGR function to achieve this.  I attached another sample app with two text boxes, one that identifies a the largest YoY difference for all customers that does this (text box 1), I still can't figure out how to do this for the customer with current year sales <= 0 (text box 2).

[sunny_talwar · Accepted solution]

This

=Concat(DISTINCT {<[Corporate Account] = {"=Rank(Sum({$<[Year]={[$(=vPY_Year)]}, [Corporate Account] = {""=Sum({$<[Year]={[$(=vCY_Year)]}>}[Sales USD]) + Sum({1} 0) <= 0""}>}[Sales USD])-Sum({$<[Year]={[$(=vCY_Year)]}, [Corporate Account] = {""=Sum({$<[Year]={[$(=vCY_Year)]}>}[Sales USD]) + Sum({1} 0) <= 0""}>}[Sales USD])) = 1"}>} [Corporate Account])

[Brett_Bleess]

Chris, did Sunny's last post get you what you needed?  If so, be sure to use the Accept as Solution button on his post to mark it as the solution and give him credit for the help.  If you did something different, consider posting that and then mark it, and if you are still working on things, leave an update.  Here are a couple of Design Blog links that could be helpful on this one possibly for the future:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

I think that is the most useful one, there are others, you can use the following link to search the area yourself, lots of good example/explanation content in this area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[cmenszak1]

This one did what I was looking for.  Thank you for the help.