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
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.
HI
Try like this
=$(=Aggr(sum(MonthlyTarget),ProductName)) - If(Sum(Sales) = 0, Sum(MonthlyTarget), Sum(Sales))
PFA
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).
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??
HI
Can you post a sample file with expected result?
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.
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.
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.
The last column there, instead of -1054, I'd like to have 1072-1054=18 there. Thanks!
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 |