Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to sum up the distinct ID Count over 3 months and then divide in 3.
The issue is that I have to count each month first, then sum, then divide.
very easy example:
distinct IDs in May=10,
distinct IDs in June=10,
distinct IDs in July= 10.
The average IDs over 3 months is 10. (the answer I'm after)
Could anyone help me and provide the correct syntax for this calculation?
Hi Meg,
It's still the same, but the syntax looks a bit harder:
Avg(
Aggr(
Count({$<PeriodCounter= {">=$(=max(PeriodCounter)-3)
<=$(=max(PeriodCounter)-1)"}, Year=, Month=, Period=, CalendarDate= ,
[Category]={'D'} >}distinct ID)
,Month)
)
Jordy
Climber
Hi Meg,
Use this:
Avg(Aggr(Count(Distinct [ID]),[Month]))
The Count distinct will count the unique ID's. The Aggr will do this by month and the AVG will then get the average from this.
Jordy
Climber
Unfortunately it does not work with my current syntax that now looks like this:
Count({$<PeriodCounter= {">=$(=max(PeriodCounter)-3)<=$(=max(PeriodCounter)-1)"}, Year=, Month=, Period=, CalendarDate= ,[Category]={'D'} >}distinct ID)
any ideas?
Hi Meg,
It's still the same, but the syntax looks a bit harder:
Avg(
Aggr(
Count({$<PeriodCounter= {">=$(=max(PeriodCounter)-3)
<=$(=max(PeriodCounter)-1)"}, Year=, Month=, Period=, CalendarDate= ,
[Category]={'D'} >}distinct ID)
,Month)
)
Jordy
Climber
Yes!:)
I forgot to put month in.
Thank you!