
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
qlik sense moving average by rows in a day by hour
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
- Tags:
- count
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How do I aggregate on day level?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I suggest using The As-Of Table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
