Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
asrifarifin83
Contributor
Contributor

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.

asrifarifin83
Contributor
Contributor
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.

rsatish111
Contributor III
Contributor III

try this one :

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

Thanks

Sattya

asrifarifin83
Contributor
Contributor
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?

asrifarifin83
Contributor
Contributor
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.