4 Replies Latest reply: May 8, 2015 5:47 PM by Rodrick Gilliam RSS

    Sequential Rolling Weekly Growth over Last Year

    Rodrick Gilliam

      I am trying to create a year over year Growth calculation, but I keep having Nulls returned.

       

      We have 13 periods in a year, so we can't use a standard calender to get year over year information.  In order to do Rolling charts, we have sequential weeks, periods, days, etc as part of the calender table.

       

      I can't get the Aggr function to return the Previous years weekly data on the same Records in the chart using the sequential columns. I can use the Aggr function to return prior year data within the same record when I am not using our the sequential column.


      I've also tried using the Concat, but I just not sure if I am using it right. Can anyone point me to the correct Function or syntax to get this to work.  I have attached the QVW with a chart showing my null results when I am just trying to get the difference b/w the two years data.

          • Re: Sequential Rolling Weekly Growth over Last Year
            Rodrick Gilliam

            Thank you for the response.  That document shows me how to calculate sequential rows within a table, I did review it prior creating this thread.  It is very informative, but I it isn't exactly what I am trying to do.

             

            What I am trying to accomplish is to calculate growth from one year to the next for specific matching weeks of the fiscal year.  Since we use a non traditional calender, the only way to show the last 12 weeks when there is a change in year is to have Sequential numbering of Weeks, Periods, Days, etc.   Meaning when live the chart will be able to show the last 12 weeks even when the new year begins.

             

            So in essence I need to show in my chart 12 weeks of data. But that data doesn't accumulate rows of data it is only showing me the results of the calculations.  I want to be able to compare Period 4 week 2 of 2014 to Period 4 week 2 of 2015 and so on.  If this happened to be the beginning of the fiscal year, I would be able to see Period 12 week 1 of 2014 through Period 1 week 4 of the 2015 which I could compare to Period 12 week 1 of 2013 through Period 1 week 4 of 2014.

              • Re: Sequential Rolling Weekly Growth over Last Year
                Gysbert Wassenaar

                What I think you should do is create an AsOf table with records that relate a week with the same week a year ago. The script below is just an example. I don't know how you handle the 365th and 4-yearly 366th day. Perhaps it's easier to generate an AsOf table in Excel and load it from that.

                 

                Qualify *;

                UnQualify wk_cnt;

                 

                AsOf:

                LOAD

                     fiscal_yr,

                     prd_of_year,

                     wk_of_prd,

                     wk_of_yr,

                     wk_cnt as AsOf.wk_cnt

                     wk_cnt - 52 as wk_cnt,

                     'YearAgo' as ReportPeriod

                RESIDENT Master;

                 

                Concatenate(AsOf)

                LOAD

                     fiscal_yr,

                     prd_of_year,

                     wk_of_prd,

                     wk_of_yr,

                     wk_cnt as AsOf.wk_cnt

                     wk_cnt,

                     'Current' as ReportPeriod

                RESIDENT Master;

                 

                UnQualify * ;

                 

                You can then use a period field from the AsOf table as dimension and expressions like:

                Current week: sum({<ReportPeriod ={'Current'}>}Value)

                Same week a year ago: sum({<ReportPeriod ={'YearAgo'}>}Value)