avg of distinct values

[Meg00]

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?

[JordyWegman]

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 

[Meg00]

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?

[JordyWegman · Accepted solution]

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

[Meg00]

Yes!:)

I forgot to put month in.

Thank you!