2 Replies Latest reply on Oct 3, 2018 9:23 AM by marcin.m

# Average monthly count by year

Hello,

I am trying to figure out how to calculate and store an average monthly count of employees for each year. Let me explain, if my data looks like this:

if I use Count Distinct, I get 3 (for 2017) and 5 (for 2018) for a year but I would prefer to get average monthly counts of (2 in 2017 and 3 in 2018), since that's how many employees were hired each month during these times:

Is there a way to display 2 and 3 in each year? I would then like to do some additional calculations based on these averages, so is there a Calculated Field function that would also give me these results? I would like to divide different numbers by these averages.

I am attaching a simple Tableau (I am using 10.3 version)

• ###### 1. Re: Average monthly count by year

Hello Marcin

You can try below logic:

Avg value= AVG({FIXED YEAR([Month]),MONTH([Month]):COUNTD([Employee Number])})

1 of 1 people found this helpful
• ###### 2. Re: Average monthly count by year

Create an LOD calculation like this:

Month Total

// Get number of employees for each month.

{FIXED YEAR([Month]), MONTH([Month]): COUNTD([Employee Number])}

Then use that field, aggregating via AVG.

See attached workbook.

1 of 1 people found this helpful