topic Calculation in QlikView

Calculation

upaliwije — Fri, 23 Jun 2017 05:21:21 GMT

I have QV document where data in the following table should be broken

My original Table is as follows

Data Table I want to extract should be as follows

If the CC_CODE = 1 premium coming same BCOD should be split.

EG BW00 Premium 107472934 and Haldumulla Premium should be deducted and shown

Pls help me

Re: Calculation

Anil_Babu_Samineni — Fri, 23 Jun 2017 05:23:49 GMT

Will you able to provide First image data in Excel rather than Image?

Re: Calculation

upaliwije — Fri, 23 Jun 2017 05:35:34 GMT

Yes of Course

Re: Calculation

ankur_abhishek — Fri, 23 Jun 2017 07:51:25 GMT

Hi UPALI

Use this formula for calculation:

if (Aggr(IF(MaxString({<[BCOD]=P([BCOD])>}[BCOD])=[BCOD],[BCOD]),[BCOD])=BCOD,

(num(if (Aggr(IF(MaxString({<[BCOD]=P([BCOD])>}[BCOD])=[BCOD],[BCOD]),[BCOD])=BCOD, PREMIUM),'##,##0')

if (Aggr(sum({$<CC_CODE={'CC*'}>}PREMIUM),BCOD)>0,Aggr(sum({$<CC_CODE={'CC*'}>}PREMIUM),BCOD),0)

),PREMIUM)

Re: Calculation

upaliwije — Fri, 23 Jun 2017 07:52:34 GMT

Hi Friends

Pls help me to help a solution for the above

Re: Calculation

ahaahaaha — Fri, 23 Jun 2017 08:19:10 GMT

Hi,

May be like this (look attached file)

Table1:

Directory;

LOAD Distinct ACCOUNTANT,

BCOD,

CC_CODE,

BRANCH,

PREMIUM

FROM

[TEST (1).xls]

(biff, embedded labels, table is Sheet1$);

NoConcatenate

Table2:

LOAD*,

If(BCOD=Previous(BCOD) And CC_CODE<>1, RangeSum(PREMIUM, peek('PREMIUM_SUM')),

If(BCOD=Previous(BCOD) And CC_CODE=1, PREMIUM*2 - RangeSum(PREMIUM, peek('PREMIUM_SUM')),

PREMIUM)) as PREMIUM_SUM,

If(BCOD=Previous(BCOD) And CC_CODE=1, PREMIUM*2 - RangeSum(PREMIUM, peek('PREMIUM_SUM')),

PREMIUM) as NEW_PREMIUM

Resident Table1

Order By BCOD asc, CC_CODE desc;

DROP Field PREMIUM_SUM;

Left Join(Table1)

LOAD*

Resident Table2;

DROP Table Table2;

Result

Regards,

Andrey

Re: Calculation

upaliwije — Fri, 23 Jun 2017 11:40:12 GMT

Could you pls send me the sample QV doc.

Re: Calculation

ankur_abhishek — Fri, 23 Jun 2017 12:02:22 GMT

PFA