Year-on-Year Pipeline - Looking for a better solution...

[#1]

G'day QlikCommunity;

Here is my conundrum:

We report in tables with the Financial Year (FY) as the dimension. This is relatively easy, we created a dimension called [Report_Period] in the script that ensures the correct dates are used.

Now the users want to see Year-on-Year view.

(ie: YOY Year-to-Date - Which is (YTD 2011 - YTD 2010)
YOY HY2011 - Which is (HY2011 - HY2010)
YOY FY 2011 - Which is (FY2011 - FY2010)
ect...

This has caused some serious headaches since they want to report on ~20 metrics. So my temporary solution for now is to have a table for each YOY period (See image - No laughing please!) and for each line (metric) I have the code as below: To create the YOY we created another [Report_Period] ingeniously called [Report_Period2] which is 12 months behind (See code below).

[CODE]
Num
(Sum(If([Report_Period] = 'YTD' AND ([Metric] ='Metric 1' OR [Metric] ='10'),[PBIT],0)*$(CX)/$(vScale))
-
Sum(If([Report_Period2] = 'YTD' AND ([Metric] ='Metric 1' OR [Metric] ='10'),[PBITD],0)*$(CX)/$(vScale)),
'$(vScaleFormat)')
[/CODE]

The CX and vScale are our Currency and Number Scaling variables...

Now using 5 tables is not the best solution and I forsee this causing more problems than I will be solving if I continue with this... The problem however is I can't think of any other way... Hoping for a golden suggestion from someone in the QlikCommunity!

-Regards

Kieran Boyce

[johnw]

I gather that these periods overlap? You could generate a table that connects these periods to the correct dates in the calendar.

Period, Date
YTD YOY, one row for each date YTD
FY 2011 YOY, one row for each date in FY 2011 YOY
HY 2012 YOY, etc.
FY 2012 YOY, etc.
FY 2013 YOY, etc.

Make a pivot table with Period a dimension and move it to the top.

I'm not sure what you're doing with the metrics themselves. But if you wanted the sum of sales for these periods, for instance, sum(Sales) would work fine.

[#3]

Below is a table showing what our periods show. There shouldn't be any overlap between the periods. (Australian FY's are July 1- Dec31)

	FY 2010	FY 2011	YOY FY2011	YTD YOY	HY 2011YOY
Jul	9	13	4	4	4
Aug	8	18	10	10	10
Sep	2	12	10	10	10
Oct	7	13	6	6	6
Nov	3	14	11	11	11
Dec	3	12	9	9	9
Jan	2	11	9	9
Feb	6	10	4	4
Mar	8	9	1
Apr	5	9	4
May	7	15	8
Jun	3	18	15
Total	63	154	91	63	50

[pover]

Considering the Metrics table, set analysis would be a good solution to define the periods that have to be calculated in the expression. For example a YTD formula cound be:

sum({$<[Fiscal Year]={$(=max([Fiscal Year]))}, [Fiscal Month]=, [Fiscal Period/Year]={"<=$(=date(max([Fiscal Period/Year]),'MM-YYYY'))"}, [Transaction Type]={'Sales Orders'}>} Amount)

You can of course save the set analysis of each different period in a variable.

[#5]

Thanks for your replies John and Karl. I guess I am a few levels below you on my QlikView knowledge, the set-analysis solution is a little confusing (Ok.. a lot confusing...)

So excuse my ignorance -

The problem is I am using 5 different tables since I can't map an expression against an expression. I don't know how to create a YOY dimension and as such I am calculating the time period in the individual expressions. This causes some serious lag when trying to load the 5 tables when opening the page or filtering anything.

I would like all the columns to be part of the one table so when I need to make a change I don't need to change across each and very expression over the 5 tables. (And the users would like it to load faster of course).

[#6 · Accepted solution]

I had some spare time today to revisit this problem I was having about 5 months ago.

I created a arbitrary YOY field inline to title the columns. I then used Set Analysis as suggested above. It's a long piece of code for an expression but at least the table is responsive (~2 seconds to load compared to the previous 30-60 seconds to load). It has made some bean-counters very happy.

Example of one of the expressions (Cut out some field names of course...) Would be happy to take on feedback/constructive criticisms.

NUM( 
IF([YOY] = 'YTD YOY',
SUM( {$<[Report Period] ={'2012 YTD'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
-
SUM( {$<[Report Period] ={'2011 YTD'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
,
IF([YOY] = 'FY 2011 YOY',
SUM( {$<[Report Period] ={'FY 2011'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
-
SUM( {$<[Report Period] ={'FY 2010'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
,
IF([YOY] = 'HY 2012 YOY',
SUM( {$<[Report Period] ={'HY 2012'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
-
SUM( {$<[Report Period] ={'HY 2011'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
,
IF([YOY] = 'FY 2012 YOY',
SUM( {$<[Report Period] ={'FY 2012'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
-
SUM( {$<[Report Period] ={'FY 2011'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
,
IF([YOY] = 'FY 2013 YOY',
SUM( {$<[Report Period] ={'FY 2013'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
-
SUM( {$<[Report Period] ={'FY 2012'}, [Objective] = {'1 -Value'}, [Act/Fcast] = {'Act/Fcast'}>} [Sales]*$(CX))
,0)
)))) /$(vScale),'$(vScaleFormat)')