Calculate moving average

[TinaH]

Hello,

I am facing the following problem: I would like to take the average of the highest and lowest 3 values of 10 days from the following table for each day of the past 5 days:

Day	Value	Average (high)	Average (low)
1	3
2	2
3	8
4	9
5	0
6	5
7	4
8	6
9	2	5	2
10	8	6,3	3,6

Example:

For day 10, I look at the values of the past 5 days, extract the highest 3 and form the average.

Range: 5, 4, 6, 2 8

Maximum 3: 5, 6, 8

Average: (5+6+8)/3 = 6,3

The same i do for the lower average calculation:

Range: 5, 4, 6, 2 8

Minimum 3: 2, 4, 5

Average: (2+4+5)/3 = 3,6

For day 9, I do the same:

Range: 0, 5, 4, 6, 2

Maximum 3: 5, 4, 6

Average: (5+4+6)/3 = 5

The same i do for the lower average calculation:

Range: 0, 5, 4, 6, 2

Minimum 3: 0, 2, 4

Average: (0+2+4)/3 = 2

This should then be done for each day. 

What formula do i use for this.

I tried something like: 

RangeMax(Above(Aggr(sum(Value),Day),0,5))

But this only gives me the maximum value of the last 5 days.

Thank you!

Best regards

Tina

[TinaH]

Any ideas?

[marcus_sommer]

It's a quite complex requirement and I think you will need at least one nested aggregation-level more as well as n if-loops to compare the results against the conditions and restrict them appropriate. This means you will probably need min/max() functions with using their second parameter or maybe rank() to find the value which should be applied as threshold. Further you may need a counting-logic because there might be several equally threshold-values which would impact the average.

More challenges are caused from the fact that probably several different aggregation-level are needed - the dimensions within the aggr() as well as the one within the TOTAL statements - and this against a rolling accumulation.

To combine everything in a single logic could be very difficult. Therefore the various parts should be developed in single steps and if they are working they might be integrated in the next higher level - or more practically used as help-calculations in n columns.

Far away from being trivial may the approach to concat() the base-aggregations with an appropriate total to a string-array and then using subfield(), substringcount() and further string- and/or pick(match()) functions to get the wanted results.

As far as the wanted view isn't depending on any user-interactions you should consider to do this work respectively the essential parts within the script.

Beside this I suggest also to questioning the business-requirement itself. Is it really necessary or helpful to build moving averages respectively the considering from outliers on this way. I could imagine that a simpler logic is even more expedient in regard to the data-literacy and the overall usability.

[Kushal_Chawda · Accepted solution]

@TinaH  I would not suggest to do it with front end expression as it will be really complex. Limitation with max function is that we cannot pass multiple column for which rangemax is used, but rangemax doesn't provide 2nd highest or 3rd highest parameter to do so which adds complexity to perform such type of calculation.

I would suggest below script approach. You might need to adjust the script based on your data granularity as first you need to aggregate your data Day wise

Data:
LOAD *
INLINE [
Day	Value
1	3	 
2	2	 
3	8 	 
4	9 	 
5	0 	 
6	5 	 
7	4 	 
8	6 	 
9	2
10	8
 
](delimiter is '\t');

Final:
NoConcatenate
Load *,
     Peek(Value,-4) as Prev_4,
     Peek(Value,-3) as Prev_3,
     Peek(Value,-2) as Prev_2,
     Peek(Value,-1) as Prev_1
Resident Data
Order by Day;

Drop Table Data;

CrossTable:
CrossTable(Day_Name,All_values,1)
LOAD Day,
     Prev_4,
     Prev_3,
     Prev_2,
     Prev_1,
     Value
Resident Final;

Left Join(Final)
Load Day,
     max(All_values) as max_value1,
     max(All_values,2) as max_value2,
     max(All_values,3) as max_value3,
     min(All_values) as min_value1,
     min(All_values,2) as min_value2,
     min(All_values,3) as min_value3
Resident CrossTable
Group by Day;

Drop Table CrossTable;

Then you can use below expressions for Avg(max) & Avg(Min) on front end

// Avg. Max
=RangeAvg(sum(max_value1),sum(max_value2),Sum(max_value3))

// Avg.Min
=RangeAvg(sum(min_value1),sum(min_value2),Sum(min_value3))