Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have got a table with a expression that calculates the GROSS MARGIN, OPERATIONAL PROFIT and would like to calculate the XNPV for it, but unfortunately it is not accepting set analysis as a parameter.
Please, see below the data model.
TABLE_1:
LOAD * INLINE [
ID, Product, GROSS PROFIT, NET REVENUE, Month, DATE, REGION
1, Pineapple, 100, 10, January, 10/01/2017, A
1, Pineapple, 150, 20, January, 15/01/2018, A
2, Apple, 100, 40, January, 20/01/2016, B
2, Apple, 50, 40, January, 20/01/2017, B
3, Melon, 30, 30, January, 25/01/2019, C
3, Melon, 100, 30, January, 25/01/2019, C
];
Calculation:
load *,
'' as [GROSS MARGIN],
'' as [OP. PROFIT],
'' as NPV
Resident TABLE_1;
drop table TABLE_1;
NoConcatenate
TMP:
load
ID,
Product,
Month,
DATE,
REGION,
[GROSS PROFIT],
[NET REVENUE],
[GROSS MARGIN],
[OP. PROFIT],
NPV
Resident Calculation;
drop table Calculation;
FINAL:
CrossTable(TYPE, REVENUE, 5)
load *
Resident TMP;
drop Table TMP;
For calculating the GROSS MARGIN and OP. PROFIT I follow the instructions from this post Calculating percentages in pivot table
using the expression as below:
=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT'),
Sum(REVENUE),
Sum(REVENUE),
Num(Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'),
Sum(REVENUE)*0.20,
Sum(REVENUE) - Sum(REVENUE)*0.20,
)
The problem is if I try to add XNPV function inside the expression it does not give me the right result, as seen below. In addition, I would like to display the result for each region separate in column. So for dimension it will have PRODUCT and TYPE.
This is only for region A:
=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),
Sum( {<REGION={'A'}>} REVENUE),
Sum( {<REGION={'A'}>} REVENUE),
Num(Sum(TOTAL <Product> {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<REGION={'A'}, TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'),
Sum( {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20,
Sum( {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>} REVENUE) - Sum( {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20,
xnpv(0.1, (Sum( {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>} REVENUE) - Sum( {<REGION={'A'}, TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20), DATE)
)
The Desirable result would be like below:
PRODUCT | TYPE | A | B | C |
---|---|---|---|---|
Pineapple | GROSS PROFIT | 250 | ||
Pineapple | NET REVENUE | 30 | ||
Pineapple | GROSS MARGIN | 833% | ||
Pineapple | GeA | 50 | ||
Pineapple | OP. PROFIT | 200 | ||
Pineapple | NPV | 188,94(from excel) | ||
Apple | GROSS PROFIT | 150 | ||
Apple | NET REVENUE | 80 | ||
Apple | GROSS MARGIN | 188% | ||
Apple | GeA | 30 | ||
Apple | OP. PROFIT | 120 | ||
Apple | NPV | 116,36 | ||
Melon | GROSS PROFIT | 130 | ||
Melon | NET REVENUE | 60 | ||
Melon | GROSS MARGIN | 217% | ||
Melon | GeA | 26 | ||
Melon | OP. PROFIT | 104 | ||
Melon | NPV | 101,81 |
Any suggestion on XNPV function?
Thank you!
The closest I could get was this:
Expression:
=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),
Sum(REVENUE),
Sum(REVENUE),
Num(Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'),
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20,
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.80,
Num(XNPV(TOTAL <Product> {<Flag = {1}>}0.10, REVENUE, DATE), '#,##0.00')
)
I think melon are off because we have same DATE for both records which could be a typo in the sample
Thank you Sunny,
The result is what I was looking for, but in my case I have a problem. I cannot flag the 'NEW GROSS PROFIT' field in my code as the value is got by a calculation used in expression. (in the problem case I have put the value straight in table to simplify my question).
Looking at your answer I would like to know if Is there a way of getting the result of last expression,
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.80 and then put in on next expression?
Num(XNPV(TOTAL <Product> (LAST EXPRESSION) 0.10, REVENUE, DATE), '#,##0.00'). Just asking in case we have another possibility of doing that. Otherwise I will have to pass the calculation of GROSS PROFIT to load script and solve the problems I was having.
Thank you.
Yes, that's true. Both XNPV and NPV works if we have more than one date at least.
Best,
Sunny
Thank you Sunny.
I appreciate your help.
Best Regards.
Here you go:
=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),
Sum(REVENUE),
Sum(REVENUE),
Num(Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'),
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20,
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.80,
Num(XNPV(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}0.10, REVENUE*0.80, DATE), '#,##0.00')
)
Hi Sunny, Thanks for your answer.
I am trying to implement it in my code. That's why I haven't gave you a feedback about that yet. In my example is working perfectly, but in code we have different gross profit for each product and it is obtained only in chart. We don't have the value in load script because when I do that I get an error and due to time I prefer to do everything in a chart.
So, one last question for that. Is that possible to do a subtraction of values? Lets take the same example and forget that GeA is 20% from GROSS PROFIT. and OP. PROFIT = GROSS PROFIT - GeA.
In this case I am trying to subtract one NPV per another (I don't know if I will get the right result, doing it to test the possibility), but the second part of expression it is not being recognized. Is there a way of doing that?
Num((XNPV(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}0.10, REVENUE, DATE) - XNPV(TOTAL <Product> {<TYPE = {'GeA'}>}0.10, REVENUE*0.2, DATE)), '#,##0.00')
Thank you very much!
This seems to be working for me.
=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),
Sum(REVENUE),
Sum(REVENUE),
Num(Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'),
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.20,
Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>} REVENUE)*0.80,
Num((XNPV(TOTAL <Product>{<TYPE = {'GROSS PROFIT'}>} 0.10, REVENUE, DATE) - XNPV(TOTAL <Product> {<TYPE = {'GeA'}>}0.10, REVENUE*0.2, DATE)), '#,##0.00')
)
Set analysis seems to be not recognized by the expression editor, but you still numbers when we click okay. The numbers don't really match your requirement, but they do populate.