COUNTING CUSTOMERS IMPACTED MORE THAN ONCE IN THE MONTH

[JulTorresGar]

Good morning.
I am trying to carry out a count, which deals with the workers who went to visit their clients more than once a month but they ask me to do it per quarter, showing how many times they had visits more than once per month.
I in a simple table add these conditions:
1.1
= if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 2), 'YYYY-MM'))"}>} [Date-calendar_date])> 1,1,0)
2.2
if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 1), 'YYYY-MM'))"}>} [Date-calendar_date])> 1 , 1.0) -
if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 2), 'YYYY-MM'))"}>} [Date-calendar_date])> 1 , 1.0)
3.3
if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 0), 'YYYY-MM'))"}>} [Date-calendar_date])> 1 , 1.0)
-
if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 1), 'YYYY-MM'))"}>} [Date-calendar_date])> 1 , 1.0)
-
if (COUNT (distinct {<Period = {"$ (= date (AddMonths (Max ([Date-calendar_date]), - 2), 'YYYY-MM'))"}>} [Date-calendar_date])> 1 , 1.0)

These formulas show 1 clients who visited their clients more than once a month and zero if they had 1 or no visits.

then I have to count by quarter which clients visited their clients more than once.
please don't know how to resolve this issue.
I hope you can help me
Thank you very much

[stevedark]

Hi @JulTorresGar 

This is a classic example of where to use an AGGR function, start by reading the help on AGGR here.

Are you looking at calendar quarters, or rolling three months? If the former I would suggest creating a Quarter field, this can be done in the load script like this:

LOAD
  [Date-calendar_date],
  Year([Date-calendar_date]) & '-Q' & ceil(Month([Date-calendar_date])/3) as Quarter,
  ... rest of your load statement ...

This can then be used in set analysis rather than getting each month separately.

If it's a rolling three months then you may want to look at >= and <= in your set analysis.

The expression you want will be something like:

sum(aggr(if(count(distinct {<Quarter={'$(=maxstring(Quarter))'}>}[Date-calendar_date])>1,1,0),Client,Quarter))

What is happening here is that the AGGR is causing the count to be done for each client in each quarter, and then a flag of zero or one to be returned into a temporary table, with Client and Quarter as virtual dimensions. The outer sum then tots the ones up to give you the count.

The beauty of this is that you could remove the set analysis and have Quarter as a dimension in your table and it would count the customers who visited each month in each quarter, not just the latest.

Hope that all makes sense?

Cheers,

Steve