13 Replies Latest reply: Nov 25, 2013 3:43 PM by Shuyu Wang

# Subtract from a fixed cell in pivot table

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

HI

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

Hi Mayil,

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

HI

Can  you post a sample file with expected result?

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

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

Hi

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

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

Hi

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 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 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

Exactly, Thank you!

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

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!

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

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

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

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

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.