-
1. Re: change date field level with parameter
Hi Doug,
Couple of solutions:
1. Use date value instead of date part when you create custom date
2. You should be able to convert the discrete to continuous by right click on the dimension
Leo
-
2. Re: change date field level with parameter
Thanks for the reply Leo,
1. I did use date value for the custom date. Thus the custom date being treated as a date field.
2. I have set the custom date field to be continuous. I put that in there in case one of the recommendations was to create a text based calculated field.
The real issue is being able to change what level the date field uses from a parameter choice.
Doug
-
3. Re: change date field level with parameter
4 of 4 people found this helpfulHi Doug,
It gets gnarly trying to account for all the things you need to. When you swap date levels of detail from Year to Quarter, it's not so bad, but if you get into Weeks or Months or Days, it can be hairy.
The simplest solution I usually go with is to create 2 views and create a parameter to swap them out. One at the Continuous Year level, and the other at Continuous Quarter. You can make filters global or mult-sheet so they'll control both views--just that one will always be hidden.
See Creating a Sheet Selector for a Dashboard | Tableau Software for reference, or attach a packaged workbook if you want assistance.
Cheers,
Mark
=====================
EDIT 8/21/2013
For posterity, attached is a related example of a workbook where there is a parameter used to swap the formatting of a measure field. But the same principle applies.
-
4. Re: change date field level with parameter
Fastest way I do this is to create a custom date for each of the dimensions I want eg m/d/y, years, quarters, months etc; create a parameter to handle the changing (as it looks prettier and I can use custom text) then create a calculated field using a simple case statement:
Case [Parameters].[Date Change]
When 'Years' Then [cfCustomDateYears]
When 'Months' Then [cfCustomDateMonths]
When 'Quarters' Then [cfCustomDateQuarters]
End
And drop that onto the relevant shelf (rows columns etc).
*** Edit ***
Forgot to add:
Also, be sure to edit your tooltip to enable your users to follow the axis, edit the tt for 'axis type' to:
axis type: <[cfCustomDateYears]><[cfCustomDateMonths]><[cfCustomDateQuarters]>
This shall change to whatever you are using and allows you to format the text and colour of each type used so you can really begin tyo draw your users eyes.
-
5. Re: change date field level with parameter
Steve,
As far as I know, parameters do not affect formatting. So when you change your parameter from 'Years' to 'Months'
you will be left with the same format.
e.g., 2012 and 2013 or Dec-12 and Jan-13. If you have a packaged workbook that achieves the change in level of detail as well as format, please do share!
This is why I recommended using different sheets rather populating a single field with the parameter. You can get the level of detail switch along with the change in formats based on parameter selection. You can of course always create a conditional text string field rather than a date field, but that's what I was referring to when I said it gets "gnarly..."
-
6. Re: change date field level with parameter
Thanks for the link Mark, I will save that for future projects. Actually I am trying to move away from the multi-version sheets. I wanted to confirm there was not a way to alter the date format via parameter.
In my situation I am also changing the measure with a table calculation for the time period, when changing from quarter to rolling 4 quarters. I am also changing the visible columns when switching from YTD to prior full year.
All that being said, I ended up going with the gnarly approach with a calculated text field. Setting the 'sort' of the text field on the date field at maximum takes care of the order of the data points. I use the last() function in a filter to control the visible marks. It is working pretty well, will still need to create some other calculated fields for the titles to mimic the date field results.
-
7. Re: Re: change date field level with parameter
2 of 2 people found this helpfulDoug, does the attached achieve what you are looking for? It uses a simple parameter to change the date type and a second calculation placed on the lod to also alter the format between years and quarters which with further tweaking, you would be able to add months, weeks and days as well (I just don't have the time).
-
8. Re: Re: change date field level with parameter
1 of 1 people found this helpfulSteve,
Yes, this is essentially what I ended up with. In your file I don't think you even need the calculated 'Date Type' field. The 'ttYear' field will do everything you need.
Though our methods were different, the outcome was the same.
Your ttYear field:
If [Parameters].[Date Type] = 'Years' Then Str(Year([Order Date (Years)]))
Else Str(DATEPART('year',[Order Date (Quarters)] )) + ' Q' +
Str(DATEPART('quarter',[Order Date (Quarters)] ))
End
My Time Period field:
if [Select a Time Period] = "YTD" or [Select a Time Period] = "Full" then
(DATEname('year', [Year]))
else (DATEname('quarter', [Year]))+"Q"+right(datename('year',[Year]),2)
end
Since my quarter string is formatted as qQyy, I have to sort by another field to avoid alphabetical sorting.
The weakness to this method is the field is not continuous, so it will not display the date range when used in a title or caption. I am creating other calculated text fields to do that when needed.
-
9. Re: change date field level with parameter
Thanks Doug; I created the ttfield to provide the ability to change the format using a parameter as requested by Mark.
Thats odd about the continuous date field as the custom dates are themselves continuous.
-
-
-
13. Re: change date field level with parameter
Doug, can you post a sample workbook for your solution. I have the problem and able to figure out a solution..
Thanks for your help and time
-
