rolling average using set analysis

igorgois_ — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: rolling average using set analysis

johnw — Mon, 27 Jun 2016 21:50:41 GMT

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.

Re: rolling average using set analysis

igorgois_ — Wed, 29 Jun 2016 17:02:57 GMT

John,

It makes sense.

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

It worked!

Lifesaver! Thank you

topic rolling average using set analysis in QlikView

rolling average using set analysis

Re: rolling average using set analysis

Re: rolling average using set analysis