topic Re: Subtract from a fixed cell in pivot table in QlikView

Subtract from a fixed cell in pivot table

Wed, 20 Nov 2013 22:30:55 GMT

Hi,

I have Monthly Target number as one line and Actual Sales as multiple lines. I would like to subtract each line item from a fixed line but don't know how to do it. eg. subtracting each month's sales from the monthly target number of 100.

Product Name Date Monthly Target Sales Delta (Result I'd like to get)

Product A 1/1/2013 0 20 100-20=80

2/1/2013 0 10 100-10=90

3/1/2013 0 40 100-40=60

1/1/2014 100 0 100-100=0

Product B 4/1/2013 0 70 100-70=30

5/1/2013 0 30 100-30=70

6/1/2013 0 40 100-40=60

1/1/2014 100 0 100-100=0

Re: Subtract from a fixed cell in pivot table

MayilVahanan — Wed, 20 Nov 2013 22:39:40 GMT

Try like this

=$(=Aggr(sum(MonthlyTarget),ProductName)) - If(Sum(Sales) = 0, Sum(MonthlyTarget), Sum(Sales))

PFA

Re: Subtract from a fixed cell in pivot table

Josh_Good — Wed, 20 Nov 2013 22:57:05 GMT

I would recommend loading the sales and the targets into different tables in the data model. Let QlikView join them together based on Product and Month. Then you will be able to do simple subtraction of Sum(Sales) - Sum(Target).

Re: Subtract from a fixed cell in pivot table

Thu, 21 Nov 2013 15:17:27 GMT

Hi Mayil,

Thanks for your reply!

I am trying to get the first half of the function right, which is having 100 on every row. My function is like

sum({<[Data Type] = {"Monthly Target"}, [Product Name]> Qty}) but I am getting 200 at each row. if I am using yours "aggr(sum..." it only return me 100 in the first row of each product??

Re: Subtract from a fixed cell in pivot table

MayilVahanan — Thu, 21 Nov 2013 15:22:35 GMT

Can you post a sample file with expected result?

Re: Subtract from a fixed cell in pivot table

DavidFoster1 — Thu, 21 Nov 2013 15:26:11 GMT

I thought the recommended practice was to concatenate target data into the same table as the Actuals data. This then avoids the risk of synthetic keys and loops when your sales and targets share common fields.

Re: Subtract from a fixed cell in pivot table

Josh_Good — Thu, 21 Nov 2013 15:42:15 GMT

Yes that is a good practice if the data size is larger. It all depends on what the synthetic key table looks like. Sometime it ends up being very large so concatenation is the best approach.

Performing join in also an option but that takes processing time during the load.

Re: Subtract from a fixed cell in pivot table

DavidFoster1 — Thu, 21 Nov 2013 15:55:17 GMT

Another alternative is to load the target data into a mapping table and use an applymap function to map it to the correct product. That way you have the data values in the table making your expression much more efficient.

Re: Re: Subtract from a fixed cell in pivot table

Thu, 21 Nov 2013 15:56:07 GMT

The last column there, instead of -1054, I'd like to have 1072-1054=18 there. Thanks!

Re: Subtract from a fixed cell in pivot table

MayilVahanan — Thu, 21 Nov 2013 16:14:52 GMT

Try like this

$(=sum({<[Type of Data]={"Budget"}>}([Feb-13])))-sum({<[Type of Data]={"Sales"}>}([Feb-13]))

1/1/2013	0	0	0	1054	859	324	18
2/1/2013	0	0	0	904	1076	687	168
3/1/2013	0	0	0	1073	1072	662	-1
11/1/2013	0	0	0	981	1118	486	91
11/4/2013	1072	1063	468	0	0	0	1072

Re: Re: Subtract from a fixed cell in pivot table

Thu, 21 Nov 2013 16:31:40 GMT

Thanks! That works, but I have three products there! How do you add one more field in the formula? Please see attached file

Re: Subtract from a fixed cell in pivot table

MayilVahanan — Thu, 21 Nov 2013 17:03:38 GMT

Are you looking like this

Product Name	Validity Date	Budget-Feb	Budget-Mar	Budget-Apr	Sales-Feb	Sales-Mar	Sales-Apr	(Budget-Sales)-Feb
Product A	11/1/2013	0	0	0	0	0	0	0
Product A	11/4/2013	0	0	0	0	0	0	0
Product B	1/1/2013	0	0	0	1054	859	324	-1054
	2/1/2013	0	0	0	904	1076	687	-904
	3/1/2013	0	0	0	1073	1072	662	-1073
	11/1/2013	0	0	0	981	1118	486	-981
	11/4/2013	1072	1063	468	0	0	0	0
Product C	11/1/2013	0	0	0	0	0	0	0
Product C	11/4/2013	0	0	0	0	0	0	0
Product D	11/1/2013	0	0	0	0	0	0	0

If so,

$(=Aggr(sum({<[Type of Data]={"Budget"}>}([Feb-13]))),[Product Name])-sum({<[Type of Data]={"Sales"}>}([Feb-13]))

and presentation-> uncheck suppress null values.

Re: Subtract from a fixed cell in pivot table

Thu, 21 Nov 2013 18:01:19 GMT

Exactly, Thank you!

Re: Subtract from a fixed cell in pivot table

Mon, 25 Nov 2013 20:43:50 GMT

Hi,

If I want to do percentage difference, can I sinply divided the difference I have as above by $(=Aggr(sum({<[Type of Data]={"Budget"}>}([Feb-13]))),[Product Name])? But it didn't seem to work. The $(xxx) should only be the first term in the calculation. Is it true?? Thank you!