# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
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

1 Solution

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

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

Partner
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

Community Browser