3 Replies Latest reply on Sep 11, 2019 3:00 AM by Diego Parker

# Yearly average of calculated field

Hello everyone,

My table looks like this:

I want to calculate an yearly average of the number of active users but I can't figure it out.

When I can't use fixed and avg(Count([Active Users Count])) because I can't aggregate aggregates.

I want an extra column showing the monthly averages per year. So, per year (count of active users) / amount of months in that year. For 2017 it will be 15.5, for 2018 it will be 26.3 and for 2019 it will be 35.9.

I tried the window_avg function but I still can't get it to work properly.

My calcs:

Conv per csr per month

{ FIXED DATEPART('month',[Sent Date Time]),[CSR name]: [Conversations]}

ActiveUsers

IF [Conv per csr per month] >= 20 THEN true else

false END

Active Users Count

If [ActiveUsers] then 1 else 0 end

Sent Date

Date([Sent Date Time])

Count of Active Users

Count([Active Users Count])

I'd love to hear suggestions on how to figure this out.

Message was edited by: Jeroen Meijerink

• ###### 1. Re: Yearly average of calculated field

Hi Jeroen,

I think the calculation is more like:

AVG({FIXED [Year]: COUNT([Active Users])

Make sure you fixed any dimensions you may need.

If you just want an average in that Table, add YEAR to the view then add the average subtotals:  analysis->Totals

Hope this helps. If does, please mark it as helpful and correct so other users can refer to it. If it doesn't maybe add a workbook for reference.

Best,

Diego

• ###### 2. Re: Yearly average of calculated field

Hi Diego,

I implemented your suggestion AVG({FIXED [Year]: COUNT([Active Users Count])}) but it still doesn't give the desired result.

The calculation returns a bigger number then expected ( 200 for 2017, 330 for 2018 and multiple values for 2019.)

I'll edit my question and upload a workbook for reference in a minute.

EDIT: I can't upload an example workbook due to privacy reasons and the size of the underlying data.

Kind regards,

Jeroen

• ###### 3. Re: Yearly average of calculated field

The numbers surely have to do with the fixed dimensions. The work workbook

will be great!

On Wed, 11 Sep 2019, 10:36 Jeroen Meijerink, <tableaucommunity@tableau.com>