topic AGGR Problem - Counting instances that meet a condition (for which you need to count first) in QlikView

AGGR Problem - Counting instances that meet a condition (for which you need to count first)

MrBosch — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community

I am trying to get this done, but to no success....

From Excel I read this info:
Date Month CallerID
2019-01-01 Jan 3ababab
2019-01-28 Jan 3ababab
2019-01-01 Jan 2efefefef
2019-02-01 Feb 3ababab
2019-02-02 Feb 3ababab
2019-02-03 Feb 3ababab
2019-03-01 Mar 3ababab
2019-03-02 Mar 2efefefef
2019-03-03 Mar 2efefefef
2019-03-03 Mar 2efefefef
2019-03-03 Mar 2efefefef
2019-03-03 Mar 2efefefef

I need find, per month, the persons (CallerID's) that have called once, twice, three times and four times or more.

So the result should be

Month: (Number of people who called) once; twice; three times; four times or more

January: 1;1;0;0 (Expl. 2efefef called once, 3ababab called two times)
February: 0;0;1;0 (Expl. 3ababab called three times)
March: 1;0;0;1

Hope it is clear what I try to achieve ... and someone has an idea how to solve this...

Highly appreciated, that's for sure.

Re: AGGR Problem (for me... )

sunny_talwar — Thu, 01 Aug 2019 18:29:25 GMT

Something like this?

Re: AGGR Problem (for me... )

MrBosch — Fri, 02 Aug 2019 07:50:04 GMT

Hi Sunny_Talwar

Thanks for your prompt reply. What I miss is the 'condition part' unfortunately (how often do these people call).
Let me try to formulate the problem statement again, perhaps it was unclear: I need to find the number of unique CallerID's (persons), that -per month- call us, given the condition that they called 'once', 'twice', 'three' and 'four times or more'.

So in a example bar graph it should look like this:

Month = January

In red are my handwritten 'conditions' signifying how often people called us in that month. So the dark blue (962) bar represent: 962 people calling us 1 time. The red bar shows the number of people (250) calling us two times (in January).

[The graph is incorrect and only used to test many formula's on my side in trying to understand what the formula's that I have entered represent]

Re: AGGR Problem (for me... )

sunny_talwar — Thu, 01 Aug 2019 20:54:03 GMT

I still don't think I understand...

Re: AGGR Problem (for me... )

MrBosch — Sat, 03 Aug 2019 01:37:24 GMT

Found a solution after a long, long day of learning a lot and a zillion trial-and-errors!

In the expression I use:
=COUNT({<CallerID = {"=COUNT(CallerID)=3"}>}DISTINCT CallerID)

To determine the total number op (unique/distinct) people, who called us (for this example) three times.

Might be easy for the experts but perhaps it can help some other beginners; the syntax is still a hand full.

Kudos to me.😅