Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Minumum of Average value in last 12 weeks

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

7 Replies
swuehl
MVP
MVP

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))

Not applicable
Author

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.

WeekRegionSalesSpend
W41b12284535228439.954
W42b13238367669429.920
W43b15877728613581.626
W44b15130271625494.224
W45b14562625696192.721
W46b13473281510205.126
W47b13989638460500.730
W48b13359397669878.920
W49b16396766108359415
W50b14968634485293.231
W51b14512086501171.629
W52b10267173449641232724

Thanks and best regards,

Chanel

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach some sample file with dates.

Regards,

Jagan.

Not applicable
Author

Hi Jagan,

Here you go.

Thanks and best regards,

Chanel

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable
Author

Thanks so much Jagan!
but how if there are 52 weeks but i just want to show latest 12 weeks?

Best regards,

Chanel

jagan
Luminary Alumni
Luminary Alumni

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.