Subtract from a fixed cell in pivot table

[#1]

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

[MayilVahanan]

HI

Try like this

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

PFA

[Josh_Good]

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

[#4]

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

[MayilVahanan]

HI

Can  you post a sample file with expected result?

[DavidFoster1]

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.

[Josh_Good]

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.

[DavidFoster1]

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.

[#9]

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

[MayilVahanan]

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