Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
John,
It makes sense.
I did a cartesian product (left join without link keys) between product and calendar table.
It worked!
Lifesaver! Thank you