Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator 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.

CHDRNUMBAT_MONTHProductPremium
123451CNT-23400
123452CNT23400
34561MAT5000
45671NUC10000
89073NUC23000
123453CNT-23400
123454CNT23400
123455CNT23400
45672NUC-10000
23451MAC5000
23452MAC-5000
23453MAC5000
23454MAC5000
45675NUC10000
45676NUC10000
119001GMC30000
119002GMC-30000

 

 

@sunny_talwar 

@swuehl 

@Gysbert_Wassenaar 

8 Replies
sunny_talwar

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?

sunny_talwar

May be an expression like this?

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

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.

sunny_talwar

Then replace the 0 with the expression

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

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.

sunny_talwar

I have no idea what you mean 🙂

pra_kale
Creator III
Creator III
Author

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.

sunny_talwar

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?