topic Re: Set Analysis - Quarter over Quarter Comparison in QlikView

Set Analysis - Quarter over Quarter Comparison

Anonymous — Sat, 16 Mar 2013 23:50:47 GMT

I am creating some KPIs and I need compare data from the current quarter to the previous quarter. I know that set analysis can be used to accomplish this, but I am not sure how to do it. Right now I have all of my data in a pivot table and I am calculating the % change with the following expression: Sum({$<FACTTYPE={'EXPOSURE'}>}AMOUNT)/Before(Sum({$<FACTTYPE={'EXPOSURE'}>}AMOUNT))-1.

I would only like to show the current quarter in the pivot and the % change for the previous quarter. I am using a master calendar in my script. Additionally, I would like to use a straight table, instead of a pivot table, so that I use the sparkline minichart feature to show a monthly trend for this metric.

All suggestion are appreciated.

Thanks in advance!

LET varMinDate = Num(MakeDate(2012,1,1)); 
LET varMaxDate = Floor(MonthEnd(Today())); 
LET varToday = Date(Today(), 'MM/DD/YYYY');
LET varYesterday = Date(Today()-1, 'MM/DD/YYYY');
LET varCurrentMonth = Date(Monthstart(Today()-1), 'MMM-YYYY');
//************************Temporary Calendar***************************
 
//Create all days in the range from varMinDate to varMaxDate
TempCalendar:
  
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());

//**************************Master Calendar******************************
 
//Building the master calendar with most date dimensions
MasterCalendar:
LOAD 
TempDate as Daily_Date,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
Week(TempDate) as Week,
Month(TempDate) as Month,
Year(TempDate) as Year,
MonthName(TempDate) AS MonthYear,
'Q' & ceil(month(TempDate) / 3)  AS Quarter,
'Q' & ceil(month(TempDate) / 3) & ' '&Year(TempDate) AS QuarterYear,
date(Today()-1) as Yesterday,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag  
 
resident TempCalendar 
order by TempDate Asc;
Drop Table TempCalendar;

Re: Set Analysis - Quarter over Quarter Comparison

Mon, 18 Mar 2013 08:59:43 GMT

Try:

Sum({$<FACTTYPE={'EXPOSURE'}, QuarterYear = {'Q4 2012'} >} AMOUNT) / (Sum({$<FACTTYPE={'EXPOSURE'}, QuarterYear = {'Q3 2012'} >}AMOUNT))-1

If you manage to get this working, all you need to do is replace the hard coded values for quarters with a dollar-sign expansion to make the expression dynamic. You can do this in many ways depending on how you want to control the values in there. One simple way is to two set variables when your script runs. Eg.:

LET vCurrentQuarter = 'Q4 2012';

LET vPreviousQuarter = 'Q3 2012';

And then use these variables in the set analysis:

Sum({$<FACTTYPE={'EXPOSURE'}, QuarterYear = {'$(vCurrentQuarter)'} >} AMOUNT) / (Sum({$<FACTTYPE={'EXPOSURE'}, QuarterYear = {'$(vPreviousQuarter)'} >}AMOUNT))-1

But you could drive the value of the dollar-sign expansion based on field selection or with a formula, too.

Re: Set Analysis - Quarter over Quarter Comparison

Anonymous — Mon, 18 Mar 2013 12:18:55 GMT

After many hours of working on my app, I did exactly what you suggested, I hard coded the QuarterYear in the expression. I also like your suggestion about setting the two variables. I will try that one as well. Thanks so much for responding.