Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have recently discovered Qlik and I am stuck with the following:
I have created a pivot table with dimensions 'Resource' and 'CollectionTime' (The granularity of the collected data is 15 minutes). The measure is 'Outbound Rate'.
I am trying to find the busy hour. For this I have created a new measure as follows:
RangeSum(Above([Outbound Rate],0,4))
However I am stuck in find the max value of the above. The goal is to find the busy hour (max of the above measure) per interface.
I have used rangemax(RangeSum(Above([Outbound Rate],0,4)),Resource,CollectionTime) but it reports the same as RangeSum(Above([Outbound Rate],0,4)).
Any help is much appreciated.
Thx in advance
PS I am using Qlik Desktop.
May be something like this
Max(TOTAL <Resource> Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))
or
Max(TOTAL Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))
Anybody?
Can you share a sample app?
May be something like this
Max(TOTAL <Resource> Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))
or
Max(TOTAL Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))
Wow, the second one works like a charm!!!!. Thank you very much!
Is it possible to return the collection time too?
May be this
FirstSortedValue(TOTAL [CollectionTime], -Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))
It works but it returns the collection time in Microsoft time stamp format.e.g. (42869)
So this does the job:
Date(Timestamp(FirstSortedValue(TOTAL [CollectionTime], -Aggr(RangeSum(Above([Outbound Rate],0,4)), ResourceName, ([CollectionTime], (Ascending))))),'DD/MM/YYYY hh:mm:ss')
Thank you Sunny for the prompt responses.