maximum value per key-field in graph

[#1]

Hi,

I have a table with turn around times.

The most important fields are: KEY, START, END, TAT, STATUS

So, now my problem is the status field can change (the value of END is the time when the status changes), so for 1 key I can have multiple lines in my table.

I only want to show the TAT in a graph of the lines where the status = 'LV'.

another problem is that 1 key can have multiple lines with STATUS = 'LV', so when that occurs, I need to take the maximum value of TAT.

So in short, i need the maximum value of TAT, per KEY, where STATUS = 'LV'.

And I want to show the sum of these values in a graph over time.

Thanks in advance!

[#2]

Hi,

Try this:

SUM(AGGR(MAX({<STATUS={LV}>} TAT),KEY,PERIOD))

NOTE: Change the PERIOD dimension above with the your time dimension.

[#3]

Thanks for your reply, but it doesn't seem to be working..

Maybe a little more info too, we want a graph with boxplots, so i need to calculate te percentiles.

For example, this is what i have for the p90:

=fractile((Aggr(max(TAT),KEY)),0.90)

Now I put a listbox for STATUS on my dashboard, and when I select 'LV', it is working, but it would be better if I can get it in my formula so we wouldn't have to make this selection every time.

[#4]

I have given you the set analysis expression in my example which will only use the 'LV' status.

[#5 · Accepted solution]

Found the right expression for my graphs.

=fractile({<STATUS={'VL'}>}(Aggr(max({<STATUS={'VL'}>}TAT),KEY)),0.90)

And this one i can use now with my drilldownlist for time (year, month, week,...) because the formula doesn't depend on 1 datefield.