topic Re: Conditional Rank with Aggr in QlikView

Conditional Rank with Aggr

cmenszak1 — Fri, 04 Oct 2019 18:43:10 GMT

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]))

Re: Conditional Rank with Aggr

JGMDataAnalysis — Mon, 14 Oct 2019 20:27:42 GMT

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])
)

Re: Conditional Rank with Aggr

qliksus — Tue, 08 Oct 2019 05:21:14 GMT

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]))

Re: Conditional Rank with Aggr

cmenszak1 — Tue, 08 Oct 2019 14:31:40 GMT

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.

Re: Conditional Rank with Aggr

sunny_talwar — Tue, 08 Oct 2019 14:39:08 GMT

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)

Re: Conditional Rank with Aggr

cmenszak1 — Tue, 08 Oct 2019 17:58:01 GMT

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).

Re: Conditional Rank with Aggr

sunny_talwar — Tue, 08 Oct 2019 18:26:03 GMT

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])

Re: Conditional Rank with Aggr

Brett_Bleess — Mon, 14 Oct 2019 12:46:51 GMT

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

Re: Conditional Rank with Aggr

cmenszak1 — Mon, 14 Oct 2019 19:54:31 GMT

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