Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks! That works, but I have three products there! How do you add one more field in the formula? Please see attached file
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.
Exactly, Thank you!
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!