topic Aggr on mulitple Dimensions in QlikView

Aggr on mulitple Dimensions

sumeet-vaidya — Fri, 19 Jan 2018 10:11:57 GMT

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

Re: Aggr on mulitple Dimensions

YoussefBelloum — Fri, 19 Jan 2018 10:14:35 GMT

Hi,

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

Re: Aggr on mulitple Dimensions

sumeet-vaidya — Fri, 19 Jan 2018 10:19:21 GMT

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

Re: Aggr on mulitple Dimensions

YoussefBelloum — Fri, 19 Jan 2018 10:29:17 GMT

Sorry, I'm seeing this:

Re: Aggr on mulitple Dimensions

sumeet-vaidya — Fri, 19 Jan 2018 10:35:40 GMT

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

Re: Aggr on mulitple Dimensions

YoussefBelloum — Fri, 19 Jan 2018 10:43:00 GMT

So if you had this

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

Re: Aggr on mulitple Dimensions

sumeet-vaidya — Fri, 19 Jan 2018 10:54:29 GMT

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

Re: Aggr on mulitple Dimensions

sunny_talwar — Fri, 19 Jan 2018 12:23:02 GMT

Try this

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

Re: Aggr on mulitple Dimensions

sumeet-vaidya — Mon, 22 Jan 2018 05:39:26 GMT

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

Re: Aggr on mulitple Dimensions

sunny_talwar — Mon, 22 Jan 2018 10:34:07 GMT

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

Re: Aggr on mulitple Dimensions

sumeet-vaidya — Mon, 22 Jan 2018 12:00:25 GMT

Hi Sunny,

I re-wrote the same expression again and its working fine.

Thank you very much stalwar1‌...!!!

Regards

Sumeet