Rolling 12 Months Average

[gaddeonline]

Hi Experts,

I have gone through all the discussions on rolling 12 months but I haven;t got what i am looking for or I may not understand what I got.

My requirement is to find out the average of average length of stay of rolling 12 months for the last 12 months. Please refer the attachment.

I will explain the requirement as much as I can.

I have last 24 months and their average length of stay(average length of stay=total discharges from hospital/total length of stay of a patient) data. so far so good. Now my client is looking for average of this average length of stay(ALOS) of each month. ex: if current month is Feb-2016, client is looking for the average of all the ALOS for the last 12 months.that is from Feb-2016 to Mar-2015. For the previous month Jan-2016, he wants Average of ALOS for all the months of Jan-2016 to Feb-2015...and this will continue for the latest 12 months.

Can any one help me in achieving this..this is my first project on Qlikview and am struggling to finish by deadline.

Thanks and Regards

Mahesh Gadde

[Chanty4u]

chk dis

Annual rolling Avgerage

N-period rolling data, moving average, easy solution.

[sunny_talwar]

May be this:

Table:

LOAD MonthYear,

    [ALOS (Days)],

    [ALOS(Rolling 12 Months)]

FROM

[Rolling 12 Months.xls]

(biff, embedded labels, table is Sheet1$);

FinalTable:

LOAD MonthYear,

  [ALOS (Days)],

  [ALOS(Rolling 12 Months)],

  If(Len(Trim(Peek('ALOS (Days)', -12))) > 0, RangeAvg([ALOS (Days)], Peek('ALOS (Days)'), Peek('ALOS (Days)', -2),

  Peek('ALOS (Days)', -3), Peek('ALOS (Days)', -4), Peek('ALOS (Days)', -5),

  Peek('ALOS (Days)', -6), Peek('ALOS (Days)', -7), Peek('ALOS (Days)', -8),

  Peek('ALOS (Days)', -9), Peek('ALOS (Days)', -10), Peek('ALOS (Days)', -11))) as [New_ALOS(Rolling 12 Months)]

Resident Table

Order By MonthYear;

DROP Table Table;

[gaddeonline]

Hi Sunny,

Thanks for your response. But you misunderstand my requirement. My attached spreadsheet is not my source. It's just an example I provided to show what i want in my qlikview report. I have month year, total discharges and total length of stay of a patient from the sql table. I calculated average length of stay in an expression. so need another expression formula to find out ALOS Rolling 12 months.

Regards

Mahesh Gadde

[gaddeonline]

Hi Chanty,

Thanks for your quick response.

I will give them a try and let you know if any thing works.

Regards

Mahesh Gadde

[sunny_talwar]

So ALOS (Days) is an expression and not a field? Would you be able to either share the raw data or the application you are working with?

[gaddeonline]

Hi Everyone,

I got the solution finally

Yes Sunny, It's not a field. it's an expression.

My solution:

I have MonthYear, Total discharges, Total Length of stay in the hospital.

So, I can easily write an expression for  Average length of stay (ALOS) =Total length of stay/Total discharges

My requirement is to create another expression called ALOS(Rolling 12 Months) =Total length of stay for rolling 12months/Total discharges for rolling 12 months.

To find out Total Length of stay for rolling 12 months, I have used the below formula:

if(CurrDate > AddMonths($(vMinDate),11,1),aggr(rangesum(above(sum(episode_length_of_stay),0,12)),MonthYear),0)

To find out hte Total discharges for rolling 12 months, I have used the below formula:

if(CurrDate > AddMonths($(vMinDate),11,1),aggr(rangesum(above(sum(Total discharges),0,12)),MonthYear),0)

Please refer the below Master Calendar Query  for the formulas like CurrDate, etc,.

//Master Calendar

/*************** MinMax Table *************

Keeps minimum and maximum Date value from Facts table

*/

MinMax:

LOAD

  Min(_date) as MinDate,

  Max(_date) as MaxDate

RESIDENT AP;

//LET vMinDate = Num('2013-07-01');

//LET vMaxDate = Num('2015-06-30');

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vCutoffDate = Date(AddYears(MonthStart($(vMinDate)),1));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

*/

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

MasterCalendar:

LOAD

TempDate AS _date,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

Week(TempDate) AS Week,

Month(TempDate) AS Month,

Year(TempDate) AS Year,

If(Num(Month(TempDate))>6,Year(TempDate)&'/'&Year(addmonths(TempDate,12)),Year(Addmonths(TempDate,-12))&'/'&Year(TempDate)) AS FinancialYear,

If(Num(Month(TempDate))>6,(Year(TempDate)-1)&'/'&(Year(addmonths(TempDate,12))-1),(Year(Addmonths(TempDate,-12))-1)&'/'&(Year(TempDate)-1)) AS PreFinancialYear,

If(Num(Month(TempDate))>6,Year(TempDate)&'-07-01',Year(AddMonths(TempDate,-12))&'-07-01') AS DateFinancialYear,

MonthStart(TempDate) as CurrDate,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

inyeartodate(TempDate, $(vToday), 0, 7) * -1 AS CurFYTDFlag,

inyeartodate(TempDate, $(vToday), -1, 7) * -1 AS LastFYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[jagan]

Hi,

Derive a month table like below

AsOfMonth:
load
Month as Month_AsOf,
Month + 1 - IterNo() as Month
Resident SalesData
while IterNo() <= 12;

Now use Month_AsOf as a dimension in your chart.

Regards,

jagan.

[jagan]

Check below link for more methods.

Calculating rolling n-period totals, averages or other aggregations

[jagan]

Also check this link more explanation in this

The As-Of Table