topic Re: Set Analysis/Aggr help? in QlikView

Set Analysis/Aggr help?

danielact — Tue, 21 Oct 2014 12:30:29 GMT

I have a chart which uses the following expression:

max(if(aggr(NODISTINCT rank(max(aggr(NODISTINCT sum(Losses),Event,Year)),4,1),Year)=20,aggr(sum(Losses),Event,Year)))

Basically, I want to get the maximum loss event for each year, then take the 20th ranked loss. This data is as-of a certain date, which is always selected.

A user asked to see it over time, and how the number has changed. So I set up a chart with the different as-of dates as a dimension, and am trying to adjust the expression accordingly, but I can't get it to work. Here's what I'm using now:

max({$<AsOfDate=>}if(aggr(NODISTINCT rank(max(aggr(NODISTINCT sum({$<AsOfDate=>}Losses),Event,Year,AsOfDate)),4,1),Year,AsOfDate)=20,aggr(sum({$<AsOfDate=>}Losses),Event,Year,AsOfDate)))

Any idea why this isn't working?

Re: Set Analysis/Aggr help?

cesaraccardi — Tue, 21 Oct 2014 13:08:09 GMT

Hi Daniel,

What about using the "rank" parameter of the max function, something like:

max(aggr(NODISTINCT sum(Losses),Event,Year)),20)

Kind Regards,

Cesar

Re: Set Analysis/Aggr help?

danielact — Tue, 21 Oct 2014 13:33:46 GMT

I never tried that, but I did figure out the solution. I needed to order the Aggr differently. If I put the date field first, before event and year, it works.

Regarding the max function, I can actually use that for something else - it had never occurred to me before. But it looks like it can't use a dimension as the rank parameter. So if I have a dimension that has values associated with it, I can't refer to the values in my max statement. They all just return the highest value.

For example, I have the following table, let's say for test scores:

Place Rank

1st 1

2nd 2

3rd 3

If I put Place as my dimension, then I do Max(TestScore, Rank) as the expression, it will always return the highest test score, for each place. Am I doing something wrong there?

Re: Set Analysis/Aggr help?

cesaraccardi — Tue, 21 Oct 2014 13:48:58 GMT

I think it doesn't work that way because a field like Rank can have multiple values and the Max function is expecting a single value. So you would have to aggregate them somehow, possibly using a variable that stores the desired rank if that makes sense. Please can you give more details about your requirement, do you want to have a different number for each line on your table?

Re: Set Analysis/Aggr help?

danielact — Tue, 21 Oct 2014 13:51:18 GMT

Yes, I'd want the highest score on the first line, the 2nd highest on the second line, etc. It should still only be returning one value, because the Rank field (bad name, I know, because there's an expression with that name) only has one value associated with the dimension.