9 Replies Latest reply on Oct 30, 2013 9:36 AM by Philip Keogh

# How can I get the count of years in my data?

Hello,

My source has sales over time. I am interested in getting the yearly sales average by dividing the sum of sales over the number of years (I know my data spans over 5 years).

When I tried to use Count([dateOfSale]), I got 651 - not sure what to use to get the number of years. I searched, but did not find anything relevant.

• ###### 1. Re: How can I get the count of years in my data?

Maybe something along the lines of MAX(DATEPART('year',[Date]))-MIN(DATEPART('year',[Date]))?  That would give you the range of your data in terms of the number of years.  Not strictly a count, but if there aren't any gaps, might work for you.

• ###### 2. Re: How can I get the count of years in my data?

You might try:

COUNT(YEAR([dateOfSale])

However, I'm not sure this is necessary to get the information you want.  Can you post a sample packaged workbook and set it up in a way that demonstrates what you are trying to accomplish--then someone can show you how to calculate the numbers you are after?

• ###### 3. Re: How can I get the count of years in my data?

Hi Sami

I have done something similar and solved the problem by...

dividing the numerator (eg Sales) by a distinct count of year (dcount[Year]).

If your year is a date/time field then you will need to extract the year portion from it (datepart('year',[Date])) and perform a distinct count on that dcount(datepart('year',[Date])).

I hope this helps

Philip

• ###### 4. Re: How can I get the count of years in my data?

Hi Mark,

This worked - but I had to add 1 to the result.

2012 - 2008 = 4, however I need to include 2008 - so I used MAX(DATEPART('year',[Date]))-MIN(DATEPART('year',[Date])) + 1

Thank you.

• ###### 5. Re: How can I get the count of years in my data?

Philip - I could not find "dcount". Do you have it in your Functions?

• ###### 6. Re: How can I get the count of years in my data?

Hi Matthew - this gave me the same result (651 - which is number of records in my file)

• ###### 7. Re: How can I get the count of years in my data?

Thank you all for your quick responses.

• ###### 8. Re: How can I get the count of years in my data?

Again, with a packaged sample workbook, someone could show you the ideal way to get at what you are looking for as an end result.  There are many ways to accomplish this, but without seeing the data or a workbook, it is difficult to provide help.

Count distinct is not available for Excel/MS Access data sources, unless you extract the data into Tableau first.  I assume that is what "dcount" referred to--in Tableau, with a supported data source or extract, it would actually be: COUNTD(YEAR([DateField]))

1 of 1 people found this helpful
• ###### 9. Re: How can I get the count of years in my data?

Hi Sami

Oops... sorry about that. It should have bee COUNTD(). As mentioned later, that function won't appear unless the underlying data source supports it, or you are using a data extract.

The nice thing that I have found using the COUNTD() approach, using the date/time field as an argument, is that it works just as well for 'days' and 'weeks' (providing you are counting them within a year boundary).

Kind regards

Philip