topic Re: AGGR & RANK with two dimensions in App Development

AGGR & RANK with two dimensions

piotr-bratek — Mon, 12 Aug 2024 19:05:09 GMT

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 ?

Re: AGGR & RANK with two dimensions

Oleg_Troyansky — Mon, 12 Aug 2024 19:27:41 GMT

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!

Re: AGGR & RANK with two dimensions

Digvijay_Singh — Mon, 12 Aug 2024 19:42:08 GMT

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.