avg daily sales for last 12 months

[#1]

Dear experts,

How do I get average daily sales for last 12 months from the month the user has selected using set analysis?

Please help.

Thank you.

Message was edited by: Pramod Karanam

[SergeyMak · Accepted solution]

Something like this

RangeAvg(AGGR(Sum({$<Year={$(='">=" & Year(AddMonths(Max(Date),-12)) & '"')},

Month={$(='">=" & Month(AddMonths(Max(Date),-12)) & '"')}

>}Sales),Month))

But it's better if you share your sample app

[jagan]

Hi,

Try like this

=Sum({<DateDimension={'>=$(=MonthStart(Max(DateDimension), -11))<=$(=Date(Max(DateDimension)))'}>}Sales)/Count({<DateDimension={'>=$(=MonthStart(Max(DateDimension), -11))<=$(=Date(Max(DateDimension)))'}>} Distinct DateDimension)

In the above expression replace DateDimension with your date field name.

Regards,

Jagan.

[#4]

Hai jagan,

I am using a Pivot table... I have attached the format of the pivot table to the main question. Could you please help.

Thank you.

[SergeyMak]

Hi Pramod,

Could you attach app, not excel?

[jagan]

Hi,

What is your expected output in the average Daily Sales Last 12 months?  Can you attach some sample Qlikview file?

Regards,

Jagan.

[#7]

you could achieve it with selecting the years you want to compare in a listbox and use the year and month as dimension in you pivottable. and then your expression would be avg(sales)

regards,

MT