Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

13 Replies
Not applicable
Author

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

MayilVahanan

Hi

Are you looking like this

Product Name Validity Date Budget-FebBudget-MarBudget-AprSales-FebSales-MarSales-Apr(Budget-Sales)-Feb
Product A11/1/20130000000
11/4/20130000000
Product B1/1/20130001054859324-1054
2/1/20130009041076687-904
3/1/201300010731072662-1073
11/1/20130009811118486-981
11/4/2013107210634680000
Product C11/1/20130000000
11/4/20130000000
Product D11/1/20130000000

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Exactly, Thank you!

Not applicable
Author

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!