Calculation

[upaliwije]

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 · Accepted solution]

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]

Thanks

I completely missed it