Aggr on mulitple Dimensions

[sumeet-vaidya]

Dear All,

I have a scenario wherein I want to calculate Repeat cases based on (PolicyNo+Subtype+MonthYear)

Secenario 1 :    

PolicyNo	Subtype	Date	Month-Year
100	ABC	5/1/2017	May-17
100	ABC	5/1/2017	May-17
100	ABC	5/4/2017	May-17
100	ABC	5/6/2017	May-17
100	ABC	5/6/2017	May-17
100	ABC	5/7/2017	May-17

Output  

Month-Year	Repeat Count
May-17	3

Scenario 2:    

PolicyNo	Subtype	Date	Month-Year
100	ABC	5/1/2017	May-17
100	XYV	5/1/2017	May-17
100	PQR	5/4/2017	May-17

Output  

Month-Year	Repeat Count
May-17	0

Scenario 3:    

PolicyNo	Subtype	Date	Month-Year
100	ABC	5/1/2017	May-17
100	ABC	5/1/2017	May-17
100	PQR	5/4/2017	May-17

Output  

Month-Year	Repeat Count
May-17	0

Awaiting your reply.

Regards

Sumeet

[YoussefBelloum]

Hi,

What is the repeat count ? and how did you calculate it on the first scenario ?

[sumeet-vaidya]

Hi,

Repeat Count is Count(PolicyNo+Subtypes+MonthYear).

If a PolicyNo has multiple entries on the same Date, then it wont be considered as repeat.

In Scenario 1 :

Count 1 = 100+ ABC+5/1/2017

Count 2 = 100+ABC+5/4/2017

Count 3 = 100+ABC+6/4/2017

Count 4 = 100+ABC+7/4/2017

If the policy count is 4, then it is been repeated thrice so the Repeat Count = 3

Regards

Sumeet

[YoussefBelloum]

Sorry, I'm seeing this:

[sumeet-vaidya]

Hi,

You are seeing it perfectly just that we would be subtracting 1 from whatever would be the count of unique records.

For Eg. When the Policy comes for the 1st time, it is Unique but when it comes 2nd time then it is repeated once, when it comes 3rd time then it is repeated twice and so on.

hope this helps

Regards

Sumeet

[YoussefBelloum]

So if you had this

in this case you will have only 4 records because you don't count the first one ?

[sumeet-vaidya]

Hi,

MonthYear is based on Date. You have mentioned Oct date in front of May-2017.

If I had above scenario with Date as 5/8/2017 then Repeat Count = 4

Regards

Sumeet

[sunny_talwar]

Try this

Count(DISTINCT PolicyNo&Subtype&Date&[Month-Year]) - Count(DISTINCT PolicyNo&Subtype&[Month-Year])

[sumeet-vaidya]

Hi,

Thanks for your reply stalwar1‌

But, This expression in not working for below cases.

Case 1 : Repeat Count = 2.

PolicyNo	SubType	MthYr1	Date
001	ABC	Apr-2017	4/1/2017
001	ABC	Apr-2017	4/18/2017
001	ABC	Apr-2017	4/28/2017
001	PQR	Apr-2017	4/25/2017

Case 2 : Repeat Count = 0

PolicyNo	SubType	Recvd_MthYr1	Date
001	ABC	Apr-2017	4/22/2017
001	PQR	Apr-2017	4/18/2017

Regards

Sumeet

[sunny_talwar · Accepted solution]

Seems to be working for me

Case 1

Case 2

One thing I had change was change the field name Subtype to SubType. Notice the upper case T? QlikView is case sensitive and you need to make sure that you use the correct casing in the field name