Using XNPV function

[Anonymous]

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!

[Anonymous]

Hi Sunny!

I have made some tests and it is working only for the first part of expression. The second part, which starts with '-' sign is ignored retrieving the wrong result.

I will try to test more and do alternative ways after I finish more tests here. As soon as I accomplish it I post results here!

Thank you very much!