Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Rolling 12 Months Average

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

9 Replies
Highlighted
Champion III
Champion III

Re: Rolling 12 Months Average

Highlighted

Re: Rolling 12 Months Average

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;


Capture.PNG

Highlighted
Contributor III
Contributor III

Re: Rolling 12 Months Average

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

Highlighted
Contributor III
Contributor III

Re: Rolling 12 Months Average

Hi Chanty,

Thanks for your quick response.

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

Regards

Mahesh Gadde

Highlighted

Re: Rolling 12 Months Average

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?

Highlighted
Contributor III
Contributor III

Re: Rolling 12 Months Average

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;

Highlighted
MVP & Luminary
MVP & Luminary

Re: Rolling 12 Months Average

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Rolling 12 Months Average

Highlighted
MVP & Luminary
MVP & Luminary

Re: Rolling 12 Months Average

Also check this link more explanation in this

The As-Of Table