-
1. Re: Showing Data Weekly based on Parameter Selection
Zhouyi ZhangJan 29, 2019 3:25 PM (in response to Rajesh Agarwal)
-
Sample file for Dates_v10.5.twbx 391.1 KB
-
-
2. Re: Showing Data Weekly based on Parameter Selection
Rajesh Agarwal Jan 29, 2019 3:46 PM (in response to Zhouyi Zhang) -
3. Re: Showing Data Weekly based on Parameter Selection
Joe OppeltJan 29, 2019 3:47 PM (in response to Rajesh Agarwal)
Zhouyi got what you needed to truncate the month name.
I want to direct you to a different approach for handling granularity like you want.
In the attached on Sheet 2 I made a new parameter that has a different string value for the internal value, and I retained the display value you were using.
The DATETRUNC function can do wonders for you when it comes to date handling. It requires a specific (all lowercase) value to denote the date part (which is why I used the values I did in the new parameter.) DATETRUNC returns a value that is the first day of the specified date part. See [Date Granular].I had to do some special handling for 'year' because of the fiscal year start, but month and quarter take care of themselves in the ELSE clause. For 'week' I needed to specify your week start date.
Regarding the year, I might be off by 12 months. July might actually start the NEXT fiscal year, not the fiscal year of the current calendar year, so maybe the dateadd offset is going in the wrong direction, but I'm just trying to demonstrate the principle here, and if you want to use this, you can adjust accordingly. But in short what I'm doing for year is to truncate to the month, and then shift x-many months to move the output value to July.
The point of this is that once you have a granular date, then you can build your display string off of that. Let Tableau do the date math first, and then you'll have an output value that is easy to format.
-
Sample file for Dates_v10.5.twbx 440.2 KB
-
-
4. Re: Showing Data Weekly based on Parameter Selection
Rajesh Agarwal Jan 29, 2019 4:57 PM (in response to Joe Oppelt)Hi Joe,
Your answer is awesome. You got what we needed keeping date functionality. But I am not able to format the way I want.
We want to show users like this.
When Yearly Data than 2018, 2019 and so on
When Quarterly Data then Yr 2018 Q1, Year 2018 Q2, (User may select multiple years from date field formatted as year)
When Monthly Data then Yr 2019 Jan
When Weekly Data Then Wk 1, Wk 2 and on on (We can add year also in week if multiple year selected from date filter)
Basically viz is like this. Based on Periods selection, graph will show weekly, monthly, quarterly or yearly data. But after selection of periods, they can further filter data for any year, quarter, month or week.
Thanks!
-
5. Re: Showing Data Weekly based on Parameter Selection
Zhouyi ZhangJan 29, 2019 5:16 PM (in response to Rajesh Agarwal)
Hi, Rajesh
week is much more complicated as there are couple of conditions need to be clarify, e.g. if a week cross two FY.
Attached is the sample of my understanding, if a week has the dates of a new FY, and then make it as the first week of the FY even though in that week, there are couple of days is before 1st of July.
Hope this helps
ZZ
-
Sample file for Dates_v10.5.twbx 393.2 KB
-
-
6. Re: Showing Data Weekly based on Parameter Selection
Rajesh Agarwal Feb 1, 2019 11:36 AM (in response to Zhouyi Zhang)Hi Zhouzi,
Your answer is good and will work for me. Only thing is that I need week to be shifted by one week. If some parts are coming in previous fiscal year, like in below case 06/30/2018 is Saturday, but this will come in next fiscal year and it will be week one. Please fix this. Thanking you for your help.
-
7. Re: Showing Data Weekly based on Parameter Selection
Zhouyi ZhangFeb 5, 2019 9:54 PM (in response to Rajesh Agarwal)
Hi, Rajesh
Please find my update.
If this solves your question, please mark my answer either correct or helpful to close the thread.
ZZ
-
Sample file for Dates_v10.5 (2).twbx 394.4 KB
-
-
8. Re: Showing Data Weekly based on Parameter Selection
Rajesh Agarwal Feb 7, 2019 11:13 AM (in response to Zhouyi Zhang)Hi Zhouyi,
You are awesome. I am struggling hard to understand the code you written but this is exactly what I needed.
Many thanks to you.
Raj