Max value of measure in pivot table

[nikolis_gr]

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.

[nikolis_gr]

Anybody?

[OmarBenSalem]

Can you share a sample app?

[sunny_talwar · Accepted solution]

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))))

[nikolis_gr]

Wow, the second one works like a charm!!!!. Thank you very much!

Is it possible to return the collection time too?

[sunny_talwar]

May be this

FirstSortedValue(TOTAL [CollectionTime], -Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))

[nikolis_gr]

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.