Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nikolis_gr
Contributor III
Contributor III

Max value of measure in pivot table

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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
nikolis_gr
Contributor III
Contributor III
Author

Anybody?

OmarBenSalem

Can you share a sample app?

sunny_talwar

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.