topic Re: Set Analysis for the previous fiscal year in QlikView

Set Analysis for the previous fiscal year

Sun, 28 Apr 2013 03:54:51 GMT

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

Re: Set Analysis for the previous fiscal year

Sun, 28 Apr 2013 06:06:51 GMT

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.

Re: Set Analysis for the previous fiscal year

Sun, 28 Apr 2013 06:24:33 GMT

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

Re: Set Analysis for the previous fiscal year

Sun, 28 Apr 2013 10:48:25 GMT

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;

Re: Set Analysis for the previous fiscal year

Mon, 29 Apr 2013 15:00:34 GMT

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