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: 
Anonymous
Not applicable

How to unique count?

I have these sets of data where the  masterdate is mapped to a master calendar.

CLAIM_TRANNOClaim_CodeClaim_TypeCLAIMNUMBERMasterDateOwn_Damaged_CountProperty_Damage_Count
1PVCAFOD_FEEEIBV9074913PLM01/12/201200
1PVCRCODEIBV9074913PLM01/12/201210
2PVCAFOD_FEEEIBV9074913PLM01/01/201300
2PVCRCODEIBV9074913PLM01/01/201310
3PVCAFOD_FEEEIBV9074913PLM01/01/201300
3PVCRCODEIBV9074913PLM01/01/201310
4PVCRCODEIBV9074913PLM01/03/201310
5PVCRCODEIBV9074913PLM01/04/201310
5PVTRCTPPDEIBV9074913PLM01/04/201301
6PVCRCODEIBV9074913PLM01/04/201310
6PVTRCTPPDEIBV9074913PLM01/04/201301
7PVCRCODEIBV9074913PLM01/04/201310
8PVTRCTPPDEIBV9074913PLM01/06/201401
9PVCAFOD_FEEEIBV9074913PLM01/06/201400
9PVCRCODEIBV9074913PLM01/06/201410
9PVTRCTPPDEIBV9074913PLM01/06/201401
10PVCCTODEIBV9074913PLM01/06/201510
10PVTRCTPPDEIBV9074913PLM01/06/201501
11PVCCTODEIBV9074913PLM01/06/201510
11PVTRCTPPDEIBV9074913PLM01/06/201501
13PVCCTODEIBV9074913PLM01/06/201510
13PVTRCTPPDEIBV9074913PLM01/06/201501

For this claim number, i want to unique count the claim type based on OD and TPPD only.

The results should be like this in pivot table

                   Year Month

                    201202 201301 201304 201406 201506

no of claim        1                        1

How do i use set analysis to calculate this?

22 Replies
tresesco
MVP
MVP

Assuming you have YearMonth field in master calendar.

Chart:   Pivot

Dim:     YearMonth

Exp:      Count( Distinct {<Claim_Type={OP, TPPD}>}Claim_Type)

Drag the dimension to top right.

Anonymous
Not applicable
Author

Year201220132013201320142015
MonthDecJanMarAprJunJun
No of Claims111222

im getting this result using your script above.

what i need  the od and tppd should be count as 1 and 1 based on date its transacted.

Year Month 

                    201202 201301 201304 201406 201506

no of claim        1                        1

avinashelite

Hi

tresesco‌ sir, I think quotes need to be add rite {'OP','TPPD'}


try like this:

Count( Distinct {<Claim_Type={'OP','TPPD'}>}Claim_Type)

SreeniJD
Specialist
Specialist

use this expression

count( distinct aggr( {<ClaimType = {'OP','TPPD'}>}ClaimType),YearMonth))

Kushal_Chawda

Try this

=sum(aggr(Count( Distinct {<Claim_Type={'OP', 'TPPD'}>}Claim_Type),YearMonth,CLAIMNUMBER))

tresesco
MVP
MVP

Hi Avinash,

It is optional. Quotes are required only if any string has space in between. Otherwise, it works with/wihout quotes.

Anonymous
Not applicable
Author

try this one :

count({<Claim_Type={'OP','TPPD'}>} DISTINCT CLAIMNUMBER )

Thanks

Sattya

Anonymous
Not applicable
Author

hi, not sure why but here is there result i get when use this quotes.

  

MonthDecJanAprJunJun
No of Claims00111

why when we use aggr, it will always store the count at end of month year instead beginning?

Anonymous
Not applicable
Author

When i use this function, result is zero. and there is a red line indicating error under the curly bracket before the claim type.