Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

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

Not applicable

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.

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

MVP
MVP

Re: Minumum of Average value in last 12 weeks

Hi,

Can you attach some sample file with dates.

Regards,

Jagan.

Not applicable

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

Hi Jagan,

Here you go.

Thanks and best regards,

Chanel

MVP
MVP

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

Hi,

Please find attached file for solution.

Regards,

Jagan.

Not applicable

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

MVP
MVP

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.

Community Browser