Average through the last 12 months include the selected month

[#1]

Hello,

i'm novice with QlikView and i need your help

In a Pivot table I have to calculate the average monthly sales for the last 12 months (included the selected month) for each hopital and for selected years as the folowinf exemple. i attached also an Execl for mor explenation

.

Any help will be greatly appreciated

Ghassane

[er_mohit]

Try this

sum({<Date = {">=$(=MonthStart(Max(Date), -12))<=$(=MonthEnd(Max(Date), -1))"},Year=,Month=>}Sale)

or

for your convienience

sum({<Month = {">=$(=MonthStart(Max(Month), -5))<=$(=MonthEnd(Max(Month), -1))"},Month=,Year=>}Sale)

[#3]

Thanks for your ansewer mohit,

But ,I don't have any field called Date?

[#4]

It seems logic, but it doesn't work...ain't no mistake on the script or should i do something before to edit this expression?

[er_mohit]

in script write thishere bold Date is my field name of Date

in load script where your date field mentioned write this

date(Date,'MMM-YYYY')AS DATE,

date(Date,'DD-MM-YYYY')AS Date,

and in expression use above expression but change sum to avg

hope it helps