Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using XNPV function

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:

PRODUCTTYPEAB

C

Pineapple

GROSS PROFIT250
PineappleNET REVENUE30
PineappleGROSS MARGIN833%

Pineapple

GeA50
PineappleOP. PROFIT200
PineappleNPV188,94(from excel)

Apple

GROSS PROFIT150
AppleNET REVENUE80
AppleGROSS MARGIN188%
AppleGeA30
AppleOP. PROFIT120
AppleNPV116,36
MelonGROSS PROFIT

130

MelonNET REVENUE60
MelonGROSS MARGIN217%
MelonGeA26
MelonOP. PROFIT104
MelonNPV101,81

Any suggestion on XNPV function?

Thank you!

10 Replies
Anonymous
Not applicable
Author

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!