-
1. Re: change date field level with parameter
Gengming Chen Aug 12, 2013 11:46 AM (in response to Doug Erickson)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
Doug Erickson Aug 12, 2013 12:08 PM (in response to Gengming Chen)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
Mark Holtz Aug 21, 2013 1:44 PM (in response to Doug Erickson)Hi 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.
-
parameter-based formatting.twbx.zip 239.1 KB
-
-
4. Re: change date field level with parameter
Steve Martin Aug 13, 2013 9:38 AM (in response to Doug Erickson)1 of 1 people found this helpfulFastest 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
Mark Holtz Aug 13, 2013 1:56 PM (in response to Steve Martin)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
Doug Erickson Aug 14, 2013 5:14 AM (in response to Mark Holtz)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
Steve Martin Aug 14, 2013 5:35 AM (in response to Mark Holtz)Doug, 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).
-
Date Parameter.twbx.zip 929.9 KB
-
-
8. Re: Re: change date field level with parameter
Doug Erickson Aug 14, 2013 5:57 AM (in response to Steve Martin)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
Steve Martin Aug 14, 2013 6:50 AM (in response to Doug Erickson)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.
-
10. Re: change date field level with parameter
Ivan Monnier Apr 5, 2014 7:34 AM (in response to Doug Erickson)Hello Doug,
I think I had a similar problem, I posted a reply hereRe: How can I create a formula that will show me Current 6 months and then Previous 6 Months so I can do a variance between the two?
I hope it will help.
Best regards
-
11. Re: change date field level with parameter
sunil kumar Aug 5, 2015 2:05 AM (in response to Steve Martin)This is very useful to me thanks a lot
-
12. Re: change date field level with parameter
sophia han Aug 5, 2015 3:03 PM (in response to Doug Erickson)This is very useful. Thanks!
-
13. Re: change date field level with parameter
arthi.keat Apr 12, 2016 7:51 PM (in response to Doug Erickson)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
-
14. Re: change date field level with parameter
Allen Kei Sep 29, 2017 5:52 PM (in response to Doug Erickson)2 of 2 people found this helpful