Skip to main content

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 
Search instead for 
Did you mean: 
Bikoman
Contributor II
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.

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

Labels (1)
1 Solution

Accepted Solutions
Bikoman
Contributor II
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.

View solution in original post

5 Replies
JordyWegman
Partner - Master
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
Bikoman
Contributor II
Contributor II
Author

How do I aggregate on day level?

sunny_talwar

I suggest using The As-Of Table 

JordyWegman
Partner - Master
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
Bikoman
Contributor II
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.