Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Thanks for your ansewer mohit,
But ,I don't have any field called Date?
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?
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