3 Replies Latest reply on Dec 16, 2016 1:48 PM by Matthew Risley

# Number of quarters in a dataset

Hi everyone!

I need a formula that calculates the number of quarters in the most recent Fiscal year.  I'm making a label.  If the most recent FY has a full set of data, then I want it to say "FY15  compared to FY16".  If it does not have a full year of data, I want it to say "FY15 compared to FY16 Q1"  I have a Boolean field for current year and I created a date for last year to use in my string.  But when I do it this way, I get the aggregate/non aggregate data error.

if [Current Year]=true and COUNTD(DATEPART('quarter', [Ddat]))=4

Then

STR("Year over Year Comparing FY")+

STR (MAX(DATENAME('year', [Ddat]))) +

STR(" to FY")+

STR (MAX(DATENAME('year', [last Year])))

ELSE

STR("Year over Year Comparing FY")+

STR (MAX(DATENAME('year', [Ddat]))) +

STR ("Q") +

STR (MAX(DATENAME('quarter', [Ddat]))) +

STR(" to FY")+

STR (MAX(DATENAME('year', [last Year])))

END

I can make a dummy workbook if you want to see the real deal.

Thanks!

• ###### 1. Re: Number of quarters in a dataset

Anna,

a workbook may be nice if it's something that you can do

In the meantime i have another possible solution:

What if you used

if Max(Datepart('month',Date) > 12 then [Do the thing you're trying to do]

else [do the other thing]

You may have to change the 12 to your custom fiscal year. But what that is saying is, "If the most recent date is December- then i have full data". Now you may have to tweak this to a different 'Date Level' if need be (could even use quarter)

And this solution may not be what you need, just wanted to add a thought

1 of 1 people found this helpful
• ###### 2. Re: Number of quarters in a dataset

Figured it out and I'm not going to tell how much time it took me...

If

DATEPART('month',max([Ddat]))=6  then....  Else....  End.

Since our FY starts in July - if the max month is 6 (June) then we've got a full year and if not then we don't.

Thanks and have a great weekend!

• ###### 3. Re: Number of quarters in a dataset

Your solution is very similar to mine. Glad you figured it out!

Happy Tableau-ing!