How to get average of max 3 hours in month?

[EspenH]

Hi

I have a data set with ID, Year, Month, Day, Hour, Value. The id is the censor from where we get hourly values. Se below table for an example:

Id	Year	Month	Day	Hour	Value
100	2023	Jan	1	0	7,48
100	2023	Jan	1	1	7,34
100	2023	Jan	1	2	7,39
100	2023	Jan	1	3	7,86
100	2023	Jan	1	4	7,61
100	2023	Jan	1	5	8,67
100	2023	Jan	1	6	7,51
100	2023	Jan	1	7	7,85
100	2023	Jan	1	8	8,29
100	2023	Jan	1	9	7,52
100	2023	Jan	1	10	8,41

Usecase:

We select a censor (Id) and a time periode (Year, Month) and need to show a table

with ID, Year, Month and sum of max 3 hours in month. 

Problem:

We have some challenge to show the max 3 hours in the month if we choose more than one month.

Example  if ww choose one month, the sum of max is right:

in the screenshot above its working as it should, and i get the right numbers for sum of max 3 values.

Example if we choose more than one month:

In the screenshot above I do not get the numbers I want. Sum of max 3 values in Jan contain only two values, since there is a max value in Feb which is greater than the third largest value in Jan.

I belive this means there is something in the scope of the max part in the expression which i need to change. As it is now it cacluates max based on all the selected (months) hourly values. I would like to restrict the max calculation inside each month.

The expression we have used is:

=SUM({$<Timesverdi={">$(=MAX(Timesverdi,4))"}>}Timesverdi)

I have tried with different aggr() alternatives to set the right scope of the max part but with no luck.

I have also tried to use range() function to solve the case but with less success.

Still quite new to this..

You experts out there, please advise - is this possible?

[fldc2500]

Hi,

Would it work to change the expression to something like this: =SUM({$<Hour={">$(=MAX(Hour)-3)"}>}Value).

Here you focus on the last 3 hours instead of the max 3 values. If I test this formula with your sample data where I add another month of data with slightly different values I'm getting the right values. 

[EspenH]

Hi

Thanks for replay, but no, that is not an option.

As specified in the case I need to have the sum of 3 top hour values in each month. Your formula does not take into account the day in the time dimension and will give med a sum of all last 3 hours each day in each month.

[fldc2500]

I'm sorry, that is my bad.

I understand what you are trying to do now. You could use a rank function to rank the values in each month (or at whatever granularity you need) and then aggregate those results.

Sum(Aggr(
If(Rank(Sum(Value)) <= 3
, Sum(Value)
),
Month, Day, Hour
))

[EspenH]

Hi, thanks again.

 I have tried your expression, but the numbers are to high. I have tried to modify the expression and added the year to the aggr dimensions without any luck. I have also tried to aggr the rank function to keep the scope in the selected values (Year, Month), but get to high values.

If I understand this I need to keep both the rank and the aggr function inside my selected scope?

[Kushal_Chawda · Accepted solution]

@EspenH  Problem is with your set analysis. Set analysis works once per chart. It doesn't evaluate against each dimension value individually. Try changing your expression as below

=RangeSum(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3))

[EspenH]

Thank you very much Kushal_Chawada!! 

And thanks for reminding me one the nature of set based expressions.

[EspenH]

Hi

Your expression works perfect, I get the monthly values on the months in the table in the Avg3 column: 

Now i want to use this monthly value in another set expression to look up a value in a connected table (column Lookup_Value in table above) :

={<valueMin = {"<=$(=RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3)))"}, valueMax = {">$(=RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3)))"}>}sum(monthlyTotal)

In this column I am facing the same issue as last time, i find only one value when the previous expression is used in the modifier in the set expression. 

Is there a way to solve this?