Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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

1 Solution

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

View solution in original post

13 Replies
MayilVahanan

HI

Try like this

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

PFA

Thanks & Regards,
Mayil Vahanan R
Josh_Good
Employee
Employee

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

Not applicable
Author

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?

Thanks & Regards,
Mayil Vahanan R
DavidFoster
Specialist
Specialist

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

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.

DavidFoster
Specialist
Specialist

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.

Not applicable
Author

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/2013000105485932418
2/1/20130009041076687168
3/1/201300010731072662-1
11/1/2013000981111848691
11/4/2013107210634680001072
Thanks & Regards,
Mayil Vahanan R