topic Re: Using XNPV function in QlikView

Using XNPV function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

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!

Re: Using XNPV function

sunny_talwar — Tue, 12 Jul 2016 20:43:17 GMT

The closest I could get was this:

Expression:

=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),

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

)

Re: Using XNPV function

sunny_talwar — Tue, 12 Jul 2016 20:55:43 GMT

I think melon are off because we have same DATE for both records which could be a typo in the sample

Re: Using XNPV function

Anonymous — Tue, 12 Jul 2016 21:24:51 GMT

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.

Re: Using XNPV function

Anonymous — Tue, 12 Jul 2016 21:26:18 GMT

Yes, that's true. Both XNPV and NPV works if we have more than one date at least.

Re: Using XNPV function

sunny_talwar — Tue, 12 Jul 2016 21:29:12 GMT

We might be able to create an Aggr function here, i just created the flag for simplicity. I don't have access to my computer right now, but will try to get back to you as soon as i get home.

Best,

Sunny

Re: Using XNPV function

Anonymous — Tue, 12 Jul 2016 21:46:20 GMT

Thank you Sunny.

I appreciate your help.

Best Regards.

Re: Using XNPV function

sunny_talwar — Wed, 13 Jul 2016 00:02:14 GMT

Here you go:

=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),

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

)

Re: Using XNPV function

Anonymous — Thu, 14 Jul 2016 13:09:59 GMT

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!

Re: Using XNPV function

sunny_talwar — Fri, 15 Jul 2016 07:13:35 GMT

This seems to be working for me.

=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN', 'GeA', 'OP. PROFIT', 'NPV'),

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.

Re: Using XNPV function

Anonymous — Fri, 15 Jul 2016 12:52:14 GMT

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!