6 Replies Latest reply on Sep 5, 2018 9:01 AM by yannis vossos

# Fixed for every year or month

Is it possible to get a fixed average value for each year or month? If I have 3 years’ worth of data, I want the average sales for each year across all customers.

Potentially I want the values highlighted below: (my actual data has individual days for the invoice.)

 Customter Number Invoice Year Sale 31145 2015 7,247,636.84 33620 2015 11,173,698 40266 2015 5,603,103.59 52800 2015 5,320,413.43 53259 2015 6,296,755.91 54944 2015 9,027,988.39 69046 2015 8,820,665.81 69305 2015 3,374,193.78 69733 2015 3,318,773.41 2015 Average 6,687,025.46 31145 2016 11,864,053.47 33620 2016 12,632,416.20 40266 2016 6,296,741.14 52800 2016 9,188,927.65 53259 2016 7,563,114.74 54944 2016 10,899,695.21 69046 2016 11,357,114.15 69305 2016 6,237,254.24 69733 2016 8,558,070.90 71669 2016 12,611,159.50 2016 Average 9,720,854.72 31145 2017 1,819,446.23 33620 2017 2,116,362.98 40266 2017 1,237,000.74 52800 2017 1,879,496.06 53259 2017 1,511,226.18 54944 2017 1,464,294.53 69046 2017 776,291.86 69305 2017 937,886.55 69733 2017 1,350,725.14 71669 2017 2,652,379.12 2017 Average 1,574,510.94

Thanks!

• ###### 1. Re: Fixed for every year or month

I forgot to mention I will be using the calculation to get the average sale price for each product for each year.

• ###### 2. Re: Fixed for every year or month

Hi

this should work >>

{ FIXED DATEPART('year', [Order Date]):(sum([Sales]))}/

{ FIXED DATEPART('year', [Order Date]): countd(DATEPART('month', [Order Date]))}

I added the count of month calculation in case you were only part way in a year

Let me know if this helped

JIm

• ###### 3. Re: Fixed for every year or month

I cannot use Datepart, I already tried that. Here is the error I get:

• ###### 4. Re: Fixed for every year or month

I just noticed, you're using Tableau 10. I am using Tableau 9.3. This calculation works in 10 but not 9.3.

• ###### 5. Re: Fixed for every year or month

OK

Glad to help - I am a T10.1 user