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.
You might try:
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?
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
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
Philip - I could not find "dcount". Do you have it in your Functions?
Hi Matthew - this gave me the same result (651 - which is number of records in my file)
Thank you all for your quick responses.
1 of 1 people found this helpful
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]))
Glad you got it working.
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).