Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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?
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?
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.