Set Analysis/Aggr help?

[danielact]

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?

[cesaraccardi]

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

[danielact]

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?

[cesaraccardi]

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?

[danielact]

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.