Accumulate Values by Associated Table

jdmarlin — Mon, 17 Aug 2015 15:14:21 GMT

Hi QlikView Community!

I expanded on an idea from http://www.quickintelligence.co.uk/qlikview-accumulate-values/ to accumulate values by creating an associated period table. So far example, April 2015 refers to January to April of 2015, and November 2014 refers to January to November of 2014.

I've got a couple problems I'm looking to simplify with my script though:

there are several null values that are created in the A.Periods table
- I've tried using Len>0 , but that doesn't seem to work
periods should be created to the most recent month (August 2015) but my script continues to December 2015
- Should I use more if statements or is there a more simple approach?

//Creates periods from January 2011 to August 2015

Periods:

LOAD

     MonthName(AddMonths(MakeDate(Year(Today())-4),RecNo()-1)) as PERIOD

AutoGenerate 60-(12-Num(Month(Today())));

//Set minimum and maximum year

Temp_Periods:

LOAD

     Min(Year(PERIOD)) as MinYear,

     Max(Year(PERIOD)) as MaxYear

Resident Periods;

Let vMinYear = Peek('MinYear',0,'Temp_Periods');

Let vMaxYear = Peek('MaxYear',0,'Temp_Periods');

DROP Table Temp_Periods;

//Loop to create PERIODs and A.PERIODs from January 2011 to December 2015

//****Should be January 2011 to most recent Month & Year*****

For a = vMinYear to vMaxYear

     For b = 1 to 12

          For i = vMinYear to vMaxYear

               For n = 1 to 12

                    A.Periods:

                    LOAD

                         Date(If($(a)=$(i), If($(b)<=$(n), MakeDate($(a), $(b)))),'MMM YYYY') as PERIOD,

                         Date(If($(i)=$(a), If($(n)>=$(b), MakeDate($(i), $(n)))),'MMM YYYY') as A.PERIOD

                    AutoGenerate(1);

               Next

          Next

     Next

Next

Thanks!

topic Accumulate Values by Associated Table in QlikView

Accumulate Values by Associated Table