qlik sense moving average by rows in a day by hour

[Bikoman]

hey guys,
I am trying to make a moving average of 15 days until today I used this formula:
rangeavg(above(sum(hasHigh)/count(hasHigh),0,15))*10. it worked, well that's what I thought.

the problem is inside my data, some records got 10 lines per day and some records got 20 lines per day (it is a changing number can also be 11,14,16...)

so I am trying to make the rangeavg function be based on the number of lines I got per day.
for example: 
for one record I got 10 lines per hour in a day so I need to look back for 150 rows for it to be 15 days, 
on the other hand, I got 15 lines per hour in a day so I need to look back for 225 rows for it to be 15 days.

LOAD
treeId,
Hour(sectionDay) as Hoursummaries,
Date(sectionDay) as Datesummaries,
Year(sectionDay) as Yearsummaries,
Month(sectionDay) as Monthsummaries,
Day(sectionDay) as Daysummaries,
Ceil(Month(sectionDay)/3) as Quartersummaries,
high,
medium,
low,
noiseRatio,
treeName,
noise,
hasHigh,
hasMedium

[JordyWegman]

Hi Bikoman,

Why don't you aggregate this data on day level? Then make a counter on how many lines there were in that day. Use this to calculate the moving average.

Jordy

Climber

[Bikoman]

How do I aggregate on day level?

[sunny_talwar]

I suggest using The As-Of Table 

[JordyWegman]

Hi,

You could try something like this:

LOAD
  Date(sectionDay) as Datesummaries,
  Year(sectionDay) as Yearsummaries,
  Month(sectionDay) as Monthsummaries,
  Day(sectionDay) as Daysummaries,
  Ceil(Month(sectionDay)/3) as Quartersummaries,
  Sum(hasHigh) as hasHigh,
  Count(hasHigh) as _cntNoOfRows
From [YourSource]
Group by Date(sectionDay),Year(sectionDay), Month(sectionDay),Day(sectionDay), Month(sectionDay)
;

Jordy

Climber

[Bikoman · Accepted solution]

Hey, 
I appreciate the help!
I have decided to not do it through the script(didn't manage to succeed there.
so I am trying to use set analysis ( not sure if this the right way to do it):
I got this variable v15day = today()-15

rangeavg(above(sum(hasHigh)/count(hasHigh),0,count({$<Datesummaries={"=Datesummaries >= v15days"}} hasHigh)))*100

I am trying to get the number of records in the last 15 days as the parameter.