topic Re: Compare Sales Differences between 2 dynamically selected sets in QlikView

Compare Sales Differences between 2 dynamically selected sets

alfredlee — Wed, 27 Jun 2012 10:42:30 GMT

I have a simple table:

ABF	YYYY	MM	CustCode	ItemCode	Sales	PM
Actual	2012	4	C01	X01	100	20
Actual	2012	5	C01	X01	100	20
Actual	2012	6	C01	X01	100	20
Actual	2012	6	C02	X01	200	45
Actual	2012	6	C02	X02	300	100
Budget	2012	4	C01	X01	100	20
Budget	2012	5	C01	X01	90	18
Budget	2012	6	C01	X01	80	16
Budget	2012	6	C02	X01	100	20
Budget	2012	6	C02	X02	500	200

I want to compare two columns dynamically, e.g. Actual vs Budget for 2012, Actual Apr vs Actual May, etc

I have tried hard coding the expressions:

Sum({$<ABF={'Actual'}>} Sales)
Sum({$<ABF={'Budget'}>} Sales)
Sum({$<ABF={'Actual'}>} Sales) - Sum({$<ABF={'Budget'}>} Sales)

They worked fine to show me the difference between Actual and Budget, and I know same will apply for Year and Month.

What I want now is to extend this so that I can have 2 list boxes of ABF, and and pick the 1st one as left hand side, the 2nd one as right hand side, and the pivot chart can display the 3 columns accordingly. I need some enlightenment as it just sounds like an easy piece of homework on set analysis. Any help would be appreciated.

Regards,

Alfred

Re: Compare Sales Differences between 2 dynamically selected sets

Anonymous — Wed, 27 Jun 2012 10:53:41 GMT

This should be an easy exercise if you are using QlikView 11. The new feature "alternate states" will allow you to set up comparitive data sets that will allow you to do side by side comparisons.

Re: Compare Sales Differences between 2 dynamically selected sets

alfredlee — Thu, 28 Jun 2012 01:56:35 GMT

Cool, it works fine! Thanks! Seems I am just lucky to have v11 with me.

Only some minor issue, there is a red syntax warning below the last closing curly. The pivot chart is working fine though, just this red mark I am puzzled.

Re: Compare Sales Differences between 2 dynamically selected sets

alfredlee — Thu, 28 Jun 2012 07:28:45 GMT

One more thing, the Show Partial Sum is not following the other filters...

This is the actual expression:

Sum({[GroupLeft] <ABF=GroupABFLeft, YYYY=GroupYYYYLeft, MM=GroupMMLeft>} Sales * ExRate) / SymbolValue

Thanks!