topic maximum value per key-field in graph in QlikView

maximum value per key-field in graph

Wed, 30 Mar 2011 08:12:22 GMT

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!

maximum value per key-field in graph

Wed, 30 Mar 2011 08:24:52 GMT

Hi,

Try this:

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

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

maximum value per key-field in graph

Wed, 30 Mar 2011 10:27:52 GMT

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.

maximum value per key-field in graph

Wed, 30 Mar 2011 10:33:41 GMT

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

maximum value per key-field in graph

Wed, 30 Mar 2011 11:00:01 GMT

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.