7 Replies Latest reply: Jan 6, 2014 5:30 AM by jagan mohan rao appala

# 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

• ###### Re: Minumum of Average value in last 12 weeks

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

• ###### Re: Minumum of Average value in last 12 weeks

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

• ###### Re: Minumum of Average value in last 12 weeks

Hi,

Can you attach some sample file with dates.

Regards,

Jagan.

• ###### Re: Re: Minumum of Average value in last 12 weeks

Hi Jagan,

Here you go.

Thanks and best regards,

Chanel

• ###### Re: Re: Re: Minumum of Average value in last 12 weeks

Hi,

Please find attached file for solution.

Regards,

Jagan.

• ###### Re: Minumum of Average value in last 12 weeks

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

Best regards,

Chanel

• ###### Re: Minumum of Average value in last 12 weeks

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.