Aggregate for x number days

#1

Hi guys,

I have a simple metric (something), evaluate between two dates in DAY interval.

I can aggregate by a func rolling the first 7 values in this mode:

c3Viz(Facility.evalMetrics({start:‘2015-11-16T00:00:00.000Z’,end:‘2016-11-13T21:59:59.999Z’,ids:[‘15’], expressions:[“rolling(‘SUM’,‘energy’,900000000000000000000,0,7)”], interval:‘DAY’}))

‘energy’ is a fantasy name.

I produce this viz:

I want to take the last value for my 7 interval.
If use ‘window(MAX)’, i have issues because the last value is not always the greater. Fox example if the serie is composed of negative numbers.

is it possible to take the last value about recurrent interval ?

Thank you very much
Fra

#2

Do you want to repeat that last value in each interval or do something else? What would be the resulting time series?

#3

thk, yes. The last number is the value for all interval.
The result must be a histogram divided by interval. The value is the same (in interval 7 DAYs) and depends by aggregate rollling.

#4

You can try to use the function dayOfWeek returning the day of the week with start returning the current datetime in your time series.

Then you should do something like:
dayOfWeek(start()) == 0 ? TS : dayOfWeek(start()) == 1 ? window(‘AVG’, TS, 1, 1) : dayOfWeek(start()) == 2 ? window(‘AVG’, TS, 2, 1)…

the window(‘AVG’, TS, x, 1) just shifts x days backward the value of TS.

So depending on the day of the week you are going to grab the last day of that week and “bring it back” to have the same value for 7 days.
There might be a simpler way to do that.

#5

With x the interval number, i think.

#6

Final result:

         "DayOfWeek==1 ? window('SUM',TS,1,7): (DayOfWeek==2 ? window('SUM',TS,0,1) +window('SUM',TS,1,6)  : (DayOfWeek==3 ? window('SUM',TS,-1,2)+window('SUM',TS,1,5):(DayOfWeek==4 ? window('SUM',TS,-2,3)+window('SUM',TS,1,4) :(DayOfWeek==5 ? window('SUM',TS,-3,4) +window('SUM',TS,1,3):(DayOfWeek==6 ? window('SUM',TS,-4,5) +window('SUM',TS,1,2):(DayOfWeek==7 ? window('SUM',TS,-5,6) +window('SUM',TS,1,1) :0.0))))))";