10 Replies Latest reply: Sep 28, 2017 8:04 AM by Nikos Tsachalis

# Maximum value of sum per 4

Hi community,

You have helped me with this previous post :

In short., 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'. The goal was to find the busy hour (max of the above measure) per interface.

So I used the suggested:

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

Now, I would like to find the max value of the 4 records that are used to calculate the above. For example. if the above maximum sum is calculated from A, B, C, D, I would like to get the max (A,B,C,D)

Will P() function get me anywhere?

• ###### Re: Maximum value of sum per 4

Would you be able to share few rows of dummy data for this and the output you expect to see out of it?

• ###### Re: Maximum value of sum per 4
 RESOURCE_NAME Name SAMPLED MAX_RATE RangeSum(Above([MAX_RATE],0,4)) (Max(TOTAL Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending))))) A 31-08-17 16:30 36683135.42 133676534.27 220212595.04 A 31-08-17 16:45 40661770.98 141953999.94 220212595.04 A 31-08-17 17:00 38712481.36 149055406.82 220212595.04 A 31-08-17 17:15 45842802.23 161900189.99 220212595.04 A 31-08-17 17:30 52723194.53 177940249.10 220212595.04 A 31-08-17 17:45 54438473.9 191716952.02 220212595.04 A 31-08-17 18:00 51190219.33 204194689.99 220212595.04 A 31-08-17 18:15 55967675.71 214319563.47 220212595.04 A 31-08-17 18:30 58616226.1 220212595.04 220212595.04 A 31-08-17 18:45 52310131.25 218084252.39 220212595.04 A 31-08-17 19:00 44330279.31 211224312.37 220212595.04 A 31-08-17 19:15 39591691.92 194848328.58 220212595.04 A 31-08-17 19:30 41323221.94 177555324.42 220212595.04 B B B

I need to get the max of the the 4 values in bold in max_rate column for every resource

• ###### Re: Maximum value of sum per 4

So you want to see 58616226.1 for RESOURCE_NAME A?

• ###### Re: Maximum value of sum per 4

I want to add a new column which will report the 58616226.1  for the RESOURCE_NAME  A, exactly

• ###### Re: Maximum value of sum per 4

May be this

If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE], 0, 4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

or this

Max(TOTAL <RESOURCE_NAME> Aggr(

If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, RESOURCE_NAME, ([SAMPLED], (Ascending))))

• ###### Re: Maximum value of sum per 4

Actually this is not working exactly as I thought.

First suggestion returns nothing but the second one, it does return the maximum value but not daily.

So what I would like to do is to get the maximum value on daily basis. For this I have added one more dimension called 'date' so the new data looke like:

 RESOURCE_NAME Date SAMPLED MAX_RATE Sum4quarters MaxSum Suggestion A 9/25/2017 16:15 34280083.39 135286913.3 157740934.26 47660781.27 A 9/25/2017 16:30 36228893.19 139676051.2 157740934.26 47660781.27 A 9/25/2017 16:45 38946111.42 142850188.3 157740934.26 47660781.27 A 9/25/2017 17:00 36757460.91 146212548.9 157740934.26 47660781.27 A 9/25/2017 17:15 40359446.39 152291911.9 157740934.26 47660781.27 A 9/25/2017 17:30 40093152.46 156156171.2 157740934.26 47660781.27 A 9/25/2017 17:45 36991696.38 154201756.1 157740934.26 47660781.27 A 9/25/2017 18:00 40296639.03 157740934.3 157740934.26 47660781.27 A 9/26/2017 16:00 38296381.65 140018523.3 176719505.61 47660781.27 A 9/26/2017 16:15 31190186.52 138933547.7 176719505.61 47660781.27 A 9/26/2017 16:30 35076780.37 140049878.2 176719505.61 47660781.27 A 9/26/2017 16:45 41977729.53 146541078.1 176719505.61 47660781.27 A 9/26/2017 17:00 42967028.95 151211725.4 176719505.61 47660781.27 A 9/26/2017 17:15 41610230.8 161631769.7 176719505.61 47660781.27 A 9/26/2017 17:30 44481464.59 171036453.9 176719505.61 47660781.27 A 9/26/2017 17:45 47660781.27 176719505.6 176719505.61 47660781.27

Dimensions: Resourse Name, Date, Sampled

Measure: MAX_RATE

Sum4quarters=RangeSum(Above([MAX_RATE],0,4))

MaxSum= (Max(TOTAL <RESOURCE_NAME,Date>Aggr(RangeSum(Above([MAX_RATE],0,4)),RESOURCE_NAME,(SAMPLED) )))

Suggestion = Max(TOTAL <RESOURCE_NAME> Aggr(

If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, RESOURCE_NAME, ([SAMPLED], (Ascending))))

So for 9/25/2017 the suggestion column should have the value in green which is the max value of the 4 values that build the MaxSum. Right now it has the max of all per resource without daily separation. Any suggestion?

• ###### Re: Maximum value of sum per 4

Try this

Max(TOTAL <RESOURCE_NAME, Date> Aggr(

If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, RESOURCE_NAME, ([SAMPLED], (Ascending))))

• ###### Re: Maximum value of sum per 4

I had already tried this, but it has the same behavior, do not know why. It reports the max of all days in total, not daily