16 Replies Latest reply: Sep 2, 2011 7:12 PM by John Witherspoon RSS

    Rolling 12 Month Total

    Christian Fisher

      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

        • Re: Rolling 12 Month Total
          John Witherspoon

          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
          ;

            • Re: Rolling 12 Month Total
              Christian Fisher

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

                • Re: Rolling 12 Month Total
                  John Witherspoon

                  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.

                    • Re: Rolling 12 Month Total
                      Christian Fisher

                      John,

                                    Thanks for your help on this.  I appreciate it.

                       

                      Chris,

                      • Rolling 12 Month Total
                        Waqas Saeed

                        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.

                          • Re: Rolling 12 Month Total
                            John Witherspoon

                            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

                              • Re: Rolling 12 Month Total
                                Waqas Saeed

                                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.

                                • Re: Rolling 12 Month Total
                                  Waqas Saeed

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

                                   

                                  help.bmp

                                  • Re: Rolling 12 Month Total
                                    Waqas Saeed

                                    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

                                      • Re: Rolling 12 Month Total
                                        John Witherspoon

                                        I guess I'm not clear what you're after, either with the previous question or this one.

                                         

                                        If you want to use the week from the master calendar, for instance, you'd just use the week from the master calendar.  Did you want a rolling 12 months ending with that week or something?  Maybe you need an AsOfDate instead of an AsOfMonth?

                                         

                                        I admit that an AsOf table can confuse selections, though I'm unclear if you're having the sorts of problems I might expect.  One possibility is selecting and locking the 'current' period type.  That way, charts by default look only at the current period even when using the AsOfMonth.  And then ONLY when you want the AsOf functionality, override the period type with set analysis.  At that point, you'd probably just name all your AsOf fields to regular fields like "Month", and have to rename the fields in the real calendar, because you'd be hiding them from the user.

                                         

                                        As for the YYYY-MMM question, what would you mean by splitting apart year and month separately?  If you have a MMM field, a month(Date) field in the calendar table, I'd think you could use it just fine.  Maybe I'm not thinking of obvious problems, though.  But if the month field is in or related to your AsOf table, then you need to establish some definitions.  What does it mean, after all, to have a rolling 12 months ending with April, when the year isn't specified?  It seems meaningless.  Well, I suppose not.  You could have the as of year as a separate dimension.  So you're comparing the rolling 12 month period ending in April across years.  I suppose that makes sense.  I have no idea if you're after something like that, though.

                                          • Re: Rolling 12 Month Total
                                            Waqas Saeed

                                            Hi John,

                                             

                                            Thanks a lot again for your reply. I think I didnt ask the question clearly.

                                             

                                            Basically I have 2 tables 1 called called Japan and the other called US which I am concatenating. After concatenation, I get 1 table called Final_Table.

                                             

                                            Japan has the maximum date of June 2011 and US has the maximum date of Apr 2011.

                                             

                                            In this Final_Table I have a MonthYear value which I am using to create AsOf Table using the code below.

                                             

                                            PeriodTable:

                                            Load date(FieldValue('MonthYear',RecNo()),'MMM-YYYY') as Period

                                            AutoGenerate FieldValueCount('MonthYear')

                                            ;

                                             

                                            AsOfPeriodTable: // Creating Current and Rolling 12 period type

                                             

                                            Load

                                            Period as AsOfPeriod,

                                            'Current' as PeriodType,

                                            Period as MonthYear

                                            Resident PeriodTable;

                                             

                                            Concatenate (AsOfPeriodTable)

                                            LOAD

                                            Period as AsOfPeriod,

                                            'Rolling 12' as PeriodType,

                                            date(AddMonths(Period,1-IterNo()),'MMM-YYYY') as MonthYear

                                            Resident PeriodTable

                                            While IterNo() <= 12;

                                             

                                            Inner Join(AsOfPeriodTable)

                                            LOAD Period as MonthYear

                                            Resident PeriodTable;

                                             

                                            DROP Table PeriodTable;

                                             

                                             

                                             

                                            Once its all done, I am trying to use AsOfPeriod as my main selection.

                                             

                                            Now, when I click on Japan, the date is correctly shown in both MonthYear and AsOfPeriod but If I select US, then AsOfPeriod is shown the same as Japan. I think AsOfPeriod is picking up the maximum date for both. for Example, if I had July 2011 as the maximum date for Japan then US would also become July 2011. Thisi s shown in the picture I have attached below.

                                             

                                            AsOf Period should be exactly equal to MonthYear, isn't it? How can I fix this?

                                             

                                            As usual I would really appreciate your reply!

                                             

                                            Thanks

                                             

                                            japan.bmp

                                             

                                            US.bmp

                                            • Re: Rolling 12 Month Total
                                              Waqas Saeed

                                              Hi John,

                                               

                                              To make it easier to understand, I have attached the same application you built.

                                               

                                              The issue I have, is with the AsOf Period showing wrong dates.

                                               

                                              in the application attached you had 1 table called ExpenseTbl which had 201106 as the max date. I have concatenated another test table with ExpenseTbl which contains the max date as 201110. My test table contains period and ISGroup1.

                                               

                                              Once the concatenated table is loaded, and I click on a value in ISGroup1 thats from ExpenseTbl table, the AsOf Period shows the maximum possible selection as 201110. That means the background of 201110 is white not grey. Which should be grey, as there was no date as 201110 in the ExpenseTbl. 201110 was only in the test table which I concatenated with the ExpenseTbl. Hence, its giving a wrong impression to user.

                                               

                                              I'm not sure, whats causing this problem, is there any way to fix it?

                                               

                                              Its a bit important to what I am doing also I am learning AsOf technique thats why I am asking this.

                                               

                                              Hope its I'm not causing any inconvinience for you.

                                               

                                              Many thanks for your help in advance.

                                                • Re: Rolling 12 Month Total
                                                  John Witherspoon

                                                  Sorry for my slow response.  I've been very busy, but hopefully I can take a look today or at least some time this week.

                                                    • Re: Rolling 12 Month Total
                                                      Waqas Saeed

                                                      No problem John. Thanks for your reply. I'll wait for your response.

                                                        • Re: Rolling 12 Month Total
                                                          John Witherspoon

                                                          I swear I still plan to get around to this.  It looks like an interesting question.  Later today actually looks good unless something new comes up.  There's been a LOT new coming up recently.  I guess that's actually good instead of bad since I like my job.  *chuckle*

                                                            • Re: Rolling 12 Month Total
                                                              John Witherspoon

                                                              OK, I see what you're talking about in the file.  In a sense it's right, because 201106 IS in the 12 month rolling average ending in month 201110.  However, I can understand that you don't want to see anything past the maximum period that has been reported for a given group.  I wouldn't want to either.

                                                               

                                                              I gather that each ISGroup1 has a maximum period that has been reported, and that these periods can differ.  You only want to report data up to these periods.  If you select Total Directs, it shouldn't "magically" give you data for 201110, but only give you data up through 201106.

                                                               

                                                              ISGroup1             max(ISPeriod1)
                                                              Expenditure          201110
                                                              Sale                 201108
                                                              Total Directs        201106
                                                              Total Indirects      201106
                                                              Total Other Charges  201106
                                                              Total Revenue        201106

                                                               

                                                              One way to enforce those maximums would be to add the ISGroup1 to the AsOf table.  Generate a separate set of data for every ISGroup1.  For that matter, it seems like maybe if a month is missing from your data, in your case, you don't want to generate an AsOf for that month, as that would seem to imply that that month DOES have data.  Ah, you were already taking that into account by only starting with existing periods.  We just need to constrain it further to only periods that exist for that specific ISGroup1.

                                                               

                                                              It'll load a little more slowly, but I think then you want to start with distinct combinations of ISGroup1 and ISPeriod1 in your main table.  You can otherwise use almost exactly the same logic you had before.  I've made the minor changes in the attached file.  It at least seems to solve the problem for Total Directs.

                                                               

                                                              I'm not sure I've answered all your questions, though.  If you still have questions about year and month separately, or reporting for weeks, and other things you've mentioned but I'm not sure I've understood, please ask them again, and ideally referring to the example you posted, or modifying it to show specific problems or things you want to accomplish.