Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

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
MVP
MVP

Re: rolling average using set analysis

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
MVP
MVP

Re: rolling average using set analysis

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

Partner
Partner

Re: rolling average using set analysis

John,

It makes sense.

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

It worked!

Lifesaver! Thank you