# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Contributor III

## Unique Policy Count Required in YTD column

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

8 Replies
MVP

## Re: Unique Policy Count Required in YTD column

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?

MVP

## Re: Unique Policy Count Required in YTD column

May be an expression like this?

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

## Re: Unique Policy Count Required in YTD column

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.

MVP

## Re: Unique Policy Count Required in YTD column

Then replace the 0 with the expression

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

## Re: Unique Policy Count Required in YTD column

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.

MVP

## Re: Unique Policy Count Required in YTD column

I have no idea what you mean 🙂

Contributor III

## Re: Unique Policy Count Required in YTD column

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.

MVP

## Re: Unique Policy Count Required in YTD column

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?