Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
igorgois_
Partner - Creator
Partner - Creator

rolling average using set analysis

hello,

I am trying to calculate the rolling product revenue average of the last 4 months using set analysis in a straight table

Dimensions:

Date

Expression:

Steps:

1- Calculate the revenue of the last 4 months per product per month per year (if revenue is negative, then zero)

2- Divide the total by 4 (average per month) and then by 24 (average per workday)

3- Calculate the average revenue per weekday for each product, month and year

4- Calculate the average revenue per weekday day by day for all products

sum(

  aggr(

max(total<MONTH,YEAR,PRODUCT_ID>

  aggr(

sum(

  aggr(

  rangemax(

  rangesum(below(total

  sum({<

  ,YEAR=,MONTH=

  >}REVENUE)

  ,0,4))

  ,0)

  ,

  PRODUCT_ID,YEAR,MONTH))

  /(4*24)

  ,

  PRODUCT_ID,YEAR,MONTH)

  )

  ,

  YEAR,MONTH,PRODUCT_ID,DATE

  )

)

My issue is when a select one product, the table only shows the days that this product has a revenue. I would like to show all days of the month with its average revenue, even if the product doesnt have revenue in that day.

Does anyone can help me?

Thanks in advance

ps: i have already read this document: Calculating rolling n-period totals, averages or other aggregations

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

It's brute force, but if at all practical, maybe add records with 0 REVENUE for all missing days by product? I'm guessing it's not practical, though.

View solution in original post

2 Replies
johnw
Champion III
Champion III

It's brute force, but if at all practical, maybe add records with 0 REVENUE for all missing days by product? I'm guessing it's not practical, though.

igorgois_
Partner - Creator
Partner - Creator
Author

John,

It makes sense.

I did a cartesian product (left join without link keys) between product and calendar table.

It worked!

Lifesaver! Thank you