# New to QlikView

Discussion board where members can get started with QlikView.

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?

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

Tags (2)
1 Solution

Accepted Solutions
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.

2 Replies
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.

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