AGGR & RANK with two dimensions

[piotr-bratek]

Hi, 

(1) here's what I have in my data set
(2) here's what I want to achieve (calculated on the fly in the visual layer - line chart or a table)

I know I have to use SUM(AGGR(IF....) to make it work, as AGGR disregards chart dimensions  ... and it works...

..... BUT on the other hand I'm still curious how to interpret results given below (or the way the formulas work), as e.g. the first one looks fine ...

Once the formula condition is =1, it looks fine. 
When I change that into <=2 it returns an incorrect result.

@hic
@swuehl 
@Oleg_Troyansky  ?

[Oleg_Troyansky · Accepted solution]

Hiello Piotr,

Allow me to invite you to the Masters Summit for Qlik in Vienna on September 30th, where I'll be teaching exactly this topic in my session about Advanced Development Techniques, and we will have an opportunity to discuss these issues in great detail.

In a nutshell - sum(AGGR(IF())) works because your IF condition is evaluated in every line of the chart/table, and you are getting the accurate Rank of each Country within each Year, etc.

The same condition, in a Set Analysis filter by Country, is being evaluated outside of your chart/table, once for every Country - NOT for each Country within each Year. Hence, it works well for one Country, but not for several Countries. 

If you REALLY wanted to make it work in Set Analysis, you'd need to create a combined Field Country&Year, and then apply the same Set Analysis to that field. I believe it should work that way.

Cheers, and I hope to see you in Vienna!

[Digvijay_Singh]

While I am aligned with @Oleg comment above, just tried to edit expression with sales field, I take it as  only consider sales values for which set expression rank value applies. But it might not work with your real data, not sure about it.