Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nikolis_gr
New Contributor II

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

Re: Max value of measure in pivot table

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

6 Replies
nikolis_gr
New Contributor II

Re: Max value of measure in pivot table

Anybody?

omarbensalem
Esteemed Contributor

Re: Max value of measure in pivot table

Can you share a sample app?

MVP
MVP

Re: Max value of measure in pivot table

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
New Contributor II

Re: Max value of measure in pivot table

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

Is it possible to return the collection time too?

MVP
MVP

Re: Max value of measure in pivot table

May be this

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

nikolis_gr
New Contributor II

Re: Max value of measure in pivot table

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.