Would it be easy to use a parameter for this? E.g. 3 values, Month View, Quarter View, Year View.
Then a calculated field with an if statement:
IF([ViewParameter]=="Month") THEN [MONTHEND_DATE]
ELSEIF(([ViewParameter]=="Quarter") THEN [QUARTEREND_DATE]
ELSE THEN [YEAREND_DATE] END
Another solution would be to use the Monthend_date as a basis for the DATE hierarchy and then use subtotals for the Quarter and Years by going to Analysis -> Totals -> Add All Subtotals. Then go to the quarter Dimension and select "Add subtotal" Like in the screenshot.
I hope this is what you were looking for.
Almost, but I'm not looking for a subtotoal of the entire quarter. I'm looking for a total of all the transaction on the last DAY of the quarter, or month, or year.
The question being asked in the data would be: "What was the total value of all the accounts on the last DAY of the month, last DAY of the quarter, and last DAY of the year?"
I think where it gets complex is that if I want the user to be able to drill from year down to month, how to show the values of the LAST DAY of those time hierarchies.
ok, so it seems to be working almost. what I saw is that lets say I'm at the q3 level so the last day of q3 is 9/31/2012. If a record has transaction for lets say 8/31/2012 and not for 9/31/2012, it uses 8/31/2012 as the max date for that transaction then so it q3, I get values for 8/31 and 9/31 then but I just want 9/31 values.
oke, but i thought your data had the quarter end dates? otherwise you would need to add some checks in calculated fields to check if the date is actually the last date in that period, e.g. all last month days and last quarter days.
It does, but it seems like there are issues on the ETL side and the flags aren't correct, is there a way tableau would do a max date by date level? I guess I could also just recreate the table in Excel and blend the sources. Right?
Right, you could do that... OR... you can do a check if the date is indeed the last day of the quarter or month by doing a calculation as such:
The DATETRUNC function truncates a date to the first date of the timeperiod that is selected: in this case the quarter. To get the LAST date of a quarter, we need to be sure that the DATE is in the next quarter, then truncate, and then decrease by one day so you get the last day of the period.
A simple check to see if it is the last day would then be:
IF [date] = DATEADD('day',-1,DATETRUNC('quarter',DATEADD('month',3,#[date]#))) THEN "Last day of the quarter" ELSE "Not the last day of the quarter" END
For other periods it would be, month:
Otherwise you should indeed create a seperate XLS with the correct dates.