Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average through the last 12 months include the selected month

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

sales-hopital.bmp.

Any help will be greatly appreciated

Ghassane

4 Replies
er_mohit
Master II
Master II

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)

Not applicable
Author

Thanks for your ansewer mohit,

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

Not applicable
Author

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
Master II
Master II

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