topic Re: Set Analysis issue with YTD calculation in QlikView

Set Analysis issue with YTD calculation

Tue, 24 Jun 2014 13:35:54 GMT

Hello,

My first post here ! Apologies if I posted in the wrong place

I have 2 straight tables to show MTD and YTD income statement.

The set analysis expression for MTD is : Sum ({<MonthName={$(=ONLY(MonthName))},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000

The set analysis expression for YTD is : Sum ({<MonthID={">=1<=$(=(max(MonthID)))"},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000

MonthName is Jan, Feb, Mar, etc (user can select via a ListBox

MonthID is 1,2,3,etc

These are both created via Load Inline

The issue is both MTD and YTD numbers are the same. Can anyone help.

Thanks.

Re: Set Analysis issue with YTD calculation

Gysbert_Wassenaar — Tue, 24 Jun 2014 13:43:06 GMT

Try this expression for YTD:

Sum ({<MonthName=, MonthID={">=1<=$(=(max(MonthID)))"},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000

What is does is disregard the selection in MonthName, so that MonthID can be set to include other months too so >=1 actually does something,

Re: Set Analysis issue with YTD calculation

Tue, 24 Jun 2014 14:12:55 GMT

Thanks so much for that! It worked!!

I have another question. Below is the result. The MeasureName is a dimension and the 2014 Actual/2014 Plan/ 2013 actuals are the fact table. How do I get a row at the bottom to calculate 'Profit %' which is the 'NPS' divided by 'Operating Profit' ?

MeasureName	2014 Actual	2014 Plan	Var : Act vs Plan	Var %	2013 Actual
NPS	$68,259	$72,199	-$3,940	0.94228581	$58,617
NNS	$58,871	$63,173	-$4,302	0.92692407	$50,238
Marginal Contribution	$25,812	$28,855	-$3,043	0.88210098	$22,236
Product Contribution	$16,866	$17,651	-$785	0.95344995	$12,835
Operating Profit OP1	$9,446	$10,285	-$839	0.91118349	$5,682

Thanks.

Re: Set Analysis issue with YTD calculation

Gysbert_Wassenaar — Tue, 24 Jun 2014 14:17:27 GMT

That's somewhat tricky. Perhaps this blog post can help: How IntervalMatch Solved My Profit and Loss Dilemma