topic Re: Calculation in QlikView

Calculation

upaliwije — Mon, 13 Aug 2018 10:37:32 GMT

Hi Friend

I have follwing script to load data in my QV doc.

LOAD Month_Year,

POLICY_NO,

// REF_NO,

TRN_DATE,

PERIOD_FROM,

PERIOD_TO,

( PERIOD_TO - PERIOD_FROM)+1 as INTERVAL,

NRP

NRP/( PERIOD_TO - PERIOD_FROM)+1 AS NRP_PD,

FROM

(biff, embedded labels, table is [Correct (2)$]);

Resulting out put is like a shown below

POLICY_NO	TRN_DATE	PERIOD_FROM	PERIOD_TO	INTERVAL	MonthEnd	BAL_DAYS	BAL_DAYS1	NRP	NRP_PD
100	7/7/2017	3/22/2017	1/21/2018	306.0	7/31/2017	24.0	132.0	-12,855.72	-41.15

Here the NRP_PD value is only -41.15 but if I divide NRP/INTERVAL (-12855.72/306) it is 42.01

I can not understand the how two figures differ. Can some one help me to arrive at 42.01 from my QV calculation also.

niclaz79 — Mon, 13 Aug 2018 11:08:44 GMT

Hi,

Your mistake is here:

NRP/( PERIOD_TO - PERIOD_FROM)+1 AS NRP_PD,

What you need to write is:

NRP/(( PERIOD_TO - PERIOD_FROM)+1) AS NRP_PD

Otherwise the +1 is not added to the interval value and is added after the division instead.

What you had was:

-12855.72/305+1 = -41.15

upaliwije — Mon, 13 Aug 2018 11:14:11 GMT

Thanks

I completely missed it