topic Re: Unique Policy Count Required in YTD column in QlikView

Unique Policy Count Required in YTD column

pra_kale — Sun, 16 Jun 2019 09:19:49 GMT

Hi ,

I have attached one application in that there are MTD & YTD columns. CNT is a calculated field which is calculated by formula (if premium < 0,-1,1). I want in YTD_Policies column if summation of same CNT is > 2 then in that case i want answer as "1" which means i want to count same policy only once in YTD column. For e.g. CHDRNUM 2345 is repeated 4 times in the month 1,2,3 & 4 & from that premium for 2 month is negative so CNT is also negative for that month. CNT for that CHDRNUM appearing as 1,-1,1,1 & summation in YTD reflating as 2. Same is with CHDRNUM 4567 which is repeated 4 times and YTD summation is 2. I want the summation in YTD_Policies column should be 1 if same policy is repeated and the summation of the same CNT is >= 2.

I have attached application as well as pasted excel file below.

CHDRNUM	BAT_MONTH	Product	Premium
12345	1	CNT	-23400
12345	2	CNT	23400
3456	1	MAT	5000
4567	1	NUC	10000
8907	3	NUC	23000
12345	3	CNT	-23400
12345	4	CNT	23400
12345	5	CNT	23400
4567	2	NUC	-10000
2345	1	MAC	5000
2345	2	MAC	-5000
2345	3	MAC	5000
2345	4	MAC	5000
4567	5	NUC	10000
4567	6	NUC	10000
11900	1	GMC	30000
11900	2	GMC	-30000

@sunny_talwar

@swuehl

@Gysbert_Wassenaar

Re: Unique Policy Count Required in YTD column

sunny_talwar — Mon, 17 Jun 2019 11:57:17 GMT

So, in this example you want to see 1 for 2345 and 4567, but 0 for the other three rows for the YTD_Policies column?

Re: Unique Policy Count Required in YTD column

sunny_talwar — Mon, 17 Jun 2019 11:58:23 GMT

May be an expression like this?

If(Sum({<BAT_MONTH={"<=$(vMn)"}>} CNT) >= 2, 1, 0)

Re: Unique Policy Count Required in YTD column

pra_kale — Mon, 17 Jun 2019 15:57:16 GMT

Thanks Sunny for your Help.

What I want is if sum >=2 then 1 else what ever sum is given by that expression.

Thanks once again.

Re: Unique Policy Count Required in YTD column

sunny_talwar — Mon, 17 Jun 2019 16:03:19 GMT

Then replace the 0 with the expression

If(Sum({<BAT_MONTH={"<=$(vMn)"}>} CNT) >= 2, 1, Sum({<BAT_MONTH={"<=$(vMn)"}>} CNT))

Re: Unique Policy Count Required in YTD column

pra_kale — Mon, 17 Jun 2019 16:09:18 GMT

Thanks Sunny for your Help.

what i want is if sum of CNT is >=2 then 1 else what ever that expression is provides.

Thanks once again Sunny.

Re: Unique Policy Count Required in YTD column

sunny_talwar — Mon, 17 Jun 2019 16:11:36 GMT

I have no idea what you mean 🙂

Re: Unique Policy Count Required in YTD column

pra_kale — Mon, 17 Jun 2019 17:36:54 GMT

I mean to say if sum of CNT is >= 2 only in that case 1 is required but else 1 or 0.

E.g. for policy 12345, 3456 & 8907 sum of CNT is 1 in that case I want out-put as 1.

Re: Unique Policy Count Required in YTD column

sunny_talwar — Mon, 17 Jun 2019 17:44:54 GMT

Okay, so what is wrong with this?

If(Sum({<BAT_MONTH={"<=$(vMn)"}>} CNT) >= 2, 1, Sum({<BAT_MONTH={"<=$(vMn)"}>} CNT))

Have you tried it? It is not giving what you need?