# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Contributor II

## 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.

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

Labels (1)
• ### range average

1 Solution

Accepted Solutions
Contributor II
Author

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.

5 Replies
Partner - Master

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

Work smarter, not harder
Contributor II
Author

How do I aggregate on day level?

MVP

I suggest using The As-Of Table

Partner - Master

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

Work smarter, not harder
Contributor II
Author

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.

Tags
Community Browser