topic How to unique count? in QlikView

How to unique count?

Anonymous — Thu, 01 Oct 2015 05:28:43 GMT

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

CLAIM_TRANNO	Claim_Code	Claim_Type	CLAIMNUMBER	MasterDate	Own_Damaged_Count	Property_Damage_Count
1	PVCAF	OD_FEE	EIBV9074913PLM	01/12/2012	0	0
1	PVCRC	OD	EIBV9074913PLM	01/12/2012	1	0
2	PVCAF	OD_FEE	EIBV9074913PLM	01/01/2013	0	0
2	PVCRC	OD	EIBV9074913PLM	01/01/2013	1	0
3	PVCAF	OD_FEE	EIBV9074913PLM	01/01/2013	0	0
3	PVCRC	OD	EIBV9074913PLM	01/01/2013	1	0
4	PVCRC	OD	EIBV9074913PLM	01/03/2013	1	0
5	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
5	PVTRC	TPPD	EIBV9074913PLM	01/04/2013	0	1
6	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
6	PVTRC	TPPD	EIBV9074913PLM	01/04/2013	0	1
7	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
8	PVTRC	TPPD	EIBV9074913PLM	01/06/2014	0	1
9	PVCAF	OD_FEE	EIBV9074913PLM	01/06/2014	0	0
9	PVCRC	OD	EIBV9074913PLM	01/06/2014	1	0
9	PVTRC	TPPD	EIBV9074913PLM	01/06/2014	0	1
10	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
10	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1
11	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
11	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1
13	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
13	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1

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?

Re: How to unique count?

tresB — Thu, 01 Oct 2015 06:35:02 GMT

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.

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 06:42:28 GMT

Year	2012	2013	2013	2013	2014	2015
Month	Dec	Jan	Mar	Apr	Jun	Jun
No of Claims		1	1	1	2	2	2

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

Re: How to unique count?

avinashelite — Thu, 01 Oct 2015 06:51:55 GMT

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

try like this:

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

Re: How to unique count?

SreeniJD — Thu, 01 Oct 2015 06:52:30 GMT

use this expression

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

Re: How to unique count?

Kushal_Chawda — Thu, 01 Oct 2015 07:00:19 GMT

Try this

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

Re: How to unique count?

tresB — Thu, 01 Oct 2015 07:01:54 GMT

Hi Avinash,

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

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:07:33 GMT

try this one :

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

Thanks

Sattya

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:08:37 GMT

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

Month	Dec	Jan	Apr	Jun	Jun
No of Claims	0	0	1	1	1

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

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:13:07 GMT

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

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:16:28 GMT

I got this results.

Year	2012	2013	2013	2014	2015
Month	Dec	Jan	Apr	Jun	Jun
No of Claims	1	1	1	1	1

Re: How to unique count?

avinashelite — Thu, 01 Oct 2015 07:18:04 GMT

Thanks for the clarification sir

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:27:53 GMT

try this

=Count(Distinct({<CLAIMTYPE={'OD','TPPPD'}>}CLAIMNUMBER)

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 07:34:28 GMT

It returns 0.

=Count(Distinct({<CLAIMTYPE={'OD','TPPD'}>}CLAIMNUMBER)

Month	Dec	Jan	Apr	Jun	Jun
No of Claims	0	0	0	0	0

Re: How to unique count?

Peter_Cammaert — Thu, 01 Oct 2015 10:31:36 GMT

How do you get 201202 = 1 in your Pivot when there is no data for that period?

Re: How to unique count?

Peter_Cammaert — Thu, 01 Oct 2015 10:47:21 GMT

A minor one, tres. The first Claim_Type should be OD, not OP. Funny how all other responses except for the one from rgvavihs copied your example.

Re: How to unique count?

Anonymous — Thu, 01 Oct 2015 11:03:20 GMT

Month	Count(DISTINCT{<CLAIM_TYPE={'OD','TPPD'}>}CLAIMNUMBER)
	1
01	1
03	1
04	1
06	1
12	1

Re: How to unique count?

Peter_Cammaert — Thu, 01 Oct 2015 11:48:54 GMT

So you mean that you want to count individual CLAIMNUMBERS that have both Claim_Types OD and TPPD in the same period? Or on the same day? And count all occurrences in the same period for a particular CLAIMNUMBER as one?

And where is the data for Feb 2012? Can we add it ourselves?

Re: How to unique count?

Anonymous — Fri, 02 Oct 2015 01:06:53 GMT

Hi Peter,

There's a typo there. it should be

Year Month

201212 201301 201304 201406 201506

no of claim 1 1

The data is on transaction basis. What i need to count is for this claim there are OD and TPPD claim reported. So OD claim was first transacted on 201212 and TPPD claim was first transacted on 201304. The remaining transaction are just movements of the ClaimNumber.

Re: How to unique count?

Anonymous — Fri, 02 Oct 2015 01:12:20 GMT

And count all occurrences in the same period for a particular CLAIMNUMBER as one?

I think this is the one that i need. Means for this claimnumber. There are 1 od and 1 tppd. However it need to reflects to its first transacted month.