Rolling 12 Month Total

[#1]

Hello,

        I'm trying to create a 12 month rolling total, however I cannot get set analysis to cover  the proper period range.  I have attached an example document with problem and all data.  I created a text box within the document with additional information.

Thanks,

TheHiker

[johnw]

A set is only calculated once for the entire table, not once per row.  I'd connect an AsOfPeriod directly to 12 months of ISPeriod1 in your ExpenseTbl.  You used an Excel file instead of an inline load so I can't test it, but something like this (your periods should be defined as dates, even if they display as YYYYMM):

PeriodTbl:
LOAD date(fieldvalue('ISPeriod1',recno()),'YYYYMM') as Period
AUTOGENERATE fieldvaluecount('ISPeriod1')
;
AsOfPeriodTbl:
LOAD
Period as AsOfPeriod
,date(addmonths(Period,1-iterno()),'YYYYMM') as ISPeriod1
RESIDENT PeriodTbl
WHILE iterno() <= 12
;
INNER JOIN (AsOfPeriodTbl)
LOAD Period as ISPeriod1
RESIDENT PeriodTbl
;
DROP TABLE PeriodTbl
;

[#3]

Thanks for looking at this. Here’s the spreadsheet while I read your response in detail.

[johnw · Accepted solution]

I converted the spreadsheet into an inline load because I like self-contained files for examples.  Anyway, here's the example.  It's basically the script above, but I added a PeriodType field so that we could see both the current period number and rolling 12 month number next to each other.

[#5]

John,

              Thanks for your help on this.  I appreciate it.

Chris,

[Anonymous]

Hi John,

Thank you so much for your solution! it works perfectly for me too.

However, I do a quick question about your scrip.

I didnt understand the extract of the script below

PeriodTbl:

LOAD date(fieldvalue('ISPeriod1',recno()),'YYYYMM') as Period

AUTOGENERATE fieldvaluecount('ISPeriod1')

;

Could you please explain this to me that what we are trying to achieve with this script?

Thanks again.

[johnw]

waqqas2426 wrote:

PeriodTbl:

LOAD date(fieldvalue('ISPeriod1',recno()),'YYYYMM') as Period

AUTOGENERATE fieldvaluecount('ISPeriod1')

;

Could you please explain this to me that what we are trying to achieve with this script?

Certainly!  It's a clever little trick that I unfortunately can't take any credit for.  Let's take it piece by piece.  The main thing here is the fieldvalue() function.  This function doesn't use any of our actual tables, and instead refers to QlikView's internal data, to a list of the distinct values for a specific field.  It's like looking at a list box instead of at a table.  You can ask for fields from this list by sequence.  In an AUTOGENERATE, recno() is a counter that tells you which record you are on, which record you are currently generating.  Combining the two, we can loop through the distinct values of a particular field without loading from any tables.  The fieldvaluecount() tells us how many values there are, so how many times we have to loop.  And finally, we have to format the field explicitly as a date(), as the fieldvalue() function loses all formatting information as best I can tell.

So the load is doing this:

for each value of field "ISPeriod1"
    create a field called "Period" which is that value formatted as a 'YYYYMM' date
next

[Anonymous]

Thanks a lot John for your reply! I now understand this script completely.

I just need one advice from you. Attached image file shows the model I have in QlikView.

I have 1 fact table containing all the data, then 1 master Calendar and 1 AsOf table. Before making this new AsOf table i was using different dimensions of the master table in different chart. But now I have this new AsOf Table, which is giving me the right result for rolling 12 months but I can only use Month_Year dimension and adding Period_Type condition in every chart I have in the application.

I'm ok with adding Period Type condition in every chart, but is there any way I can get to use other dimensions of the master calendar, like monts only, year only weeks etc.

My understanding is that, in order to use Rolling12 months, I will need to use AsOf date that is Month_Year. I'm happy to use this date but it doesnt contain other dimensions that my master calender contains like, days, weeks etc which I want to use.

Hope I didnt confuse you here. My main concern is to use all the features of master calendar as I was using before and at the same time I can calculate the rolling 12 months.

[Anonymous]

sorry I forgot to attach the file showing my basic model.

[Anonymous]

Hi John,

Just a quick question. Once I have AsOf period in the form of YYYY-MMM, is there a way to get other dimentions of like Year and Month seperately. and do I have to replace the normal YYYY-MMM from the application and replace it with YYYY-MMM everywhere in the application? or I can continue using the normal YYYY-MMM and use the AsOf YYYY-MMM for rolling 12 months?

Would really appreciate your help here.

Thanks