Set Analysis for the previous fiscal year

[#1]

Hi All,

The fiscal year starts from Dec 1 for my company, I am trying to use set analysis to modify the below statment, but it seems i can't use makeDate in the statement for set analysis, anyboby would help on this?

Any ideas would be appreciated!

=Sum(if(MakeDate(Year,Month)>=MakeDate('2010','12') andFiscal (MakeDate(Year,Month)<MakeDate('2011','12')),1,0))

Regards,

Paul Wu

[#2]

Hi Paul,

Try using the date like an integer.( i.e. 20121201) And then you can use srt analysis like

SUM({<Date ={>=20121201} >} ColumnName)

You can transform your date to format above before reload on your script.

Best.

[#3]

We can implement the statment with set analysis as beow.

=(SUM({$<Year={'2010'},Month={">=12"}>} 1)

+SUM({$<Year={'2011'},Month={"<12"}>} 1))

But I am not sure if we can use functions into the set analysis, could you please kindly advise?

Thanks for your attention.

Paul Wu

[#4 · Accepted solution]

Hi Paul,

the short solution is:

sum({< theDate={">= $(=num(makedate(2012,12,01))) <= $(=num(makedate(2013,12,01)))"} >} theFact)

Now i'll show you a professional and high-performing way solving such problems. First of all you have to generate a calendar table within your script. The calendar is a matrix with date flags like isYear_Previous or isYear_Fiscal corresponding to the field theDateID. The lower limit of your calendar could be varMinDate and the upper limit for example varMaxDate. Have a look at script below. The fact table is linked to the calendar table via field theDateID. This allows calculation in this way:

     sum(theFact * isYear_Fiscal) or sum(theFact * isYear_Previous)

If you are working with large amounts of data, this is the best solution. It is really fast, reliable and easy changeable.

Hope that helps.

Best

Sacho

Have a look at the following ETL script:

// for example

set varMinDate = Makedate(year(now())-2,01,01);

set varMaxDate = Makedate(year(now()),12,31);

// this is a temp table with a list o dates

DateTable:

LOAD $(varMinDate)+IterNo()-1 AS theDate

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

// sampe table with facts

SampleFactTable:

LOAD theDate as theDateID, rand() * 1000 AS theFact

resident DateTable;

// now we build our calendar, you can add as many fields you need

CALENDAR:

LOAD

    floor(num(theDate))                as theDateID,    // this is the key to your fact table,

    theDate,

    Year(theDate)                        as theYear,

    Year(theDate)-1                     as theYear_Previous,   

    Month(theDate)                     as theMonth_ShortDesc,

    num(Month(theDate))             as theMonth,

    Dual(Date((theDate),'MMMM'),Num(Month(theDate))) as theMonth_LongDesc,

    Floor(Monthstart(theDate))      as theMonth_Start,

    Week(theDate)                     as theWeek,

    'Q ' & Num(Ceil(Month(theDate)/3),'(ROM)0') as theQuarter,

    //...and much more...

    if(Year2Date(theDate, 0, 1, $(varMaxDate)), 1, 0) as isYear_Current,    

    if(Year2Date(theDate, -1, 1, $(varMaxDate)), 1, 0) as isYear_Previous,    

   if (theDate >= makedate(year($(varMaxDate))-1, 12, 01)

        and theDate <= makedate(year($(varMaxDate)), 12, 01), 1, 0) as isYear_Fiscal    

RESIDENT DateTable where not isNull(theDate);

DROP TABLE DateTable;

[#5]

Hi Sacho,

Thank you very much for your sharing, it is a great solution although it may not be suited with my situation. Thanks a again.

Regards,

Paul Wu