Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
I would like to calculate the minimum value of average ROI in last 12 weeks.
average ROI = sum([Revenue])/ sum([Media Investment])
my expression as shown below will give me the result for whole year:
=min(total <Region> aggr( sum([Revenue])/ sum([Media Investment]), Region))
Where or how should I include this date expression in order to get the minimum of average ROI in last 12 weeks instead of whole year?
Date={"<=$(=max(Date))>=$(=Date(max(Date)-77,'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>}
Thanks and best regards,
Chanel
Try adding the set expression (though I think you are missing initial {< characters) to each of the three aggregation functions.
edit: Like this
=min( {<Date={"<=$(=max(Date))>=$(=Date(max(Date)-77,'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>}
total <Region> aggr(
sum({<Date={"<=$(=max(Date))>=$(=Date(max(Date)-77,'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>} [Revenue])
/
sum({<Date={"<=$(=max(Date))>=$(=Date(max(Date)-77,'DD/MM/YYYY'))"},Year=,Quarter=,Month=,Week=>} [Media Investment])
, Region))
Thanks for your solution but this gave me the average ROI value with overall average (sum of revenue/sum of media investment).
I wish to get the average value by weekly basis and then average for every week.
as shown in data below, i want to get average of 27 instead of 24.
Week | Region | Sales | Spend | |||
W41 | b | 12284535 | 228439.9 | 54 | ||
W42 | b | 13238367 | 669429.9 | 20 | ||
W43 | b | 15877728 | 613581.6 | 26 | ||
W44 | b | 15130271 | 625494.2 | 24 | ||
W45 | b | 14562625 | 696192.7 | 21 | ||
W46 | b | 13473281 | 510205.1 | 26 | ||
W47 | b | 13989638 | 460500.7 | 30 | ||
W48 | b | 13359397 | 669878.9 | 20 | ||
W49 | b | 16396766 | 1083594 | 15 | ||
W50 | b | 14968634 | 485293.2 | 31 | ||
W51 | b | 14512086 | 501171.6 | 29 | ||
W52 | b | 10267173 | 449641 | 23 | 27 | 24 |
Thanks and best regards,
Chanel
Hi,
Can you attach some sample file with dates.
Regards,
Jagan.
Hi Jagan,
Here you go.
Thanks and best regards,
Chanel
Hi,
Please find attached file for solution.
Regards,
Jagan.
Thanks so much Jagan!
but how if there are 52 weeks but i just want to show latest 12 weeks?
Best regards,
Chanel
Hi,
Use Set Analysis and restrict for last 12 weeks like this
=Avg({<DateDimensionName={'>=$(=WeekStart(Max(DateDimensionName), -11))<=$(=WeekEnd(Max(DateDimensionName)))'}>}ROI)
Hope this helps you.
Regards,
Jagan.