Scope of expression in set modifier in a set expresion vs bare expression in a table....how to control this?

[EspenH]

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

Based on this I have created a table with information with dimension Id, year and month and are doing a calculation of avg 3 top hour values (big thanks to Kushal_Chawda).

The expressions used is: 

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

Now I have tried to use this value (I want to use the monthly avg of top 3 hourly values) in the set modifier in a new set expression to get a value from a joined table :

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

valueMin and valueMax is from the table where i want to fetch the value of sum(monthlyTotal)

What is happening is that the set modifier in this expression is evaluated once, and not for each month as in the Avg3 column in the table above?

Can any of you experts please provide information on how I may be able to evaluate the set modifier for each line in my table? Or if there is another way.  Still a way to go to fully grasp the set based expressions...

All feedback i appreciated!

@Kushal_Chawda 

[Kushal_Chawda · Accepted solution]

@EspenH  Probably you need to use If & aggr here. You can try adjusting dimensions in aggr to work in scope with your dimensions in the table chart.

=sum(aggr(if(sum(valueMin)<=RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3))and sum(valueMax) >=RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3)) ,monthlyTotal),ID,Year,Month))

[EspenH]

Hi Kushal_Chawda

Thank you very much for replay. Your suggestion is giving med 0 on the lines, but I belive thats because the aggregated dimensions (ID, Year, Month) is not in the table from where i want to get monthlyTotal. I also changed the comapre value from sum(minValue) to minValue, i just want to compare to one value. 

The "lookup table" is below. I would like to fetch the monthlyTotal where the Avg3 is between the valueMin and the valueMax.

I also modified the dimensions and used valueMin as the dimensjon in the aggregation. I then get the right value on the first line but 0 in the other lines (months) in the selection:

Please let me know if you have an ide on how to solve this..

[EspenH]

I found a solution, thanks to you.

As i wrote in previous post I modified in conditional element in the if statement from sum(minValue) to minValue. I also changed the aggr dimensions and combined columns from main table and lookup table:

=sum(aggr(if(valueMin <= RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3)) and
valueMax > RangeAvg(max(Timesverdi),max(Timesverdi,2),max(Timesverdi,3)) ,monthlyTotal), valueMin, Year, Month))

Thank you for very good advice and pointing me in the right direction!!