How to create a date scaffold in Tableau using Relationships
Вставка
- Опубліковано 23 лип 2024
- Relationships in tableau open up new possibilities with scaffolding. In this video, I show you how to manipulate the way relationships work to build scaffolds in Tableau. In this example I show a typical use case you'll see on support desk data or patient records in Tableau where you need to be able to display the daily total of open tickets or patients in treatment. This technique doesn't require any table calcs and is record/row specific because of the way relationships work. It can also be used a mechanism for recreating network days calculations in Tableau similar to the excel function.
0:00 - Intro
1:34 - Setting up our scaffold
4:07 - Checking our main data set
5:17 - Setting up our data model to scaffold
8:20 - Checking our scaffold has worked
#tableau #salesforce #analytics #data
Follow me on Twitter: / tableautim
My recording gear & what's on my desk. kit.co/TableauTim/desk-setup
My website: www.tableautim.com/
My Screen Annotation Tool: j.mp/3HWc4Mj
My technology Channel: j.mp/3F0d28f
Share feedback and Suggestions: tableautim.canny.io/suggestions -
-
Join this channel to get access to perks:
/ @tableautim ----------
(C) 2023 TN-Media LTD. No re-use, unauthorized use, or redistribution, of this video without prior permission.
This was very helpful. Thank you!
Tim, this totally helped me solve an issue I was struggling with and trying to solve with a lot of complex calculations. Thanks so much for all of your videos and ongoing support of the Tableau community!
More great content, Tim - I constantly recommend your channel to our customers.
Much appreciated!
@@TableauTim I am a Tableau SE, btw
Great video, Tim. Crisp, articulate, great quality video and audio. Subscribed!
Thank you for creating this video! It helped me a lot with my Iron Quest project!
This was extremely helpful, thanks Tim!
Thanks Tim, this is super helpful.
Wow, that's awesome, Tim!!!
What a great video. Very descriptive and concise. I'm beginning a project at my job that will require date scaffolding and this video describes exactly what I need to do. Great job.
Glad it was helpful!
thanks for ur efforts man very helpful for me.
This was a great technique.
Good explanation!
Thank you for this video
Really brilliant many thanks mate
Great video sir. I have subscribed and will be using you as a reference going forward. Thanks a ton.
Awesome, thank you!
Thank you so much. This really helped me a lot!! Cheers :))))
You are a genius!!
Thanks
Hey here are 3 fun facts :
1- I'm currently driving a project to implement tableau in my company, and i've been more inspired to do so partially because of your video
2- I was born the 30th december
3- I'm also from african descent
Cheers and thanks for the content !
Fantastic!
Thanks Tim, such a great video, the easiest content available on the entire internet, I'm working on similar thing with the end date = null, how can I include the null end date to this?
Your able to do custom calculations for the fields. So when you press the drop down when selecting end date your going to press edit calculation and then just do IF ISNULL(End date) THEN TODAY() ELSE End date END
Hi Tim, Enjoyed the video. great to be able to resolve time periods. I like to seek your advise, How would you use data scaffolding if you have 2 sets of time periods. 1) Promo event: start and end dates and 2) baseline : baseline_start and baseline_stop? I tried your method, I can only do 1) and 2) on separate sheets? is there a way to manipulate the relationships such that I can perform both 1) and 2) correctly on the same sheet in Tableau? Hope you can advise please?
Thanks Tim! Awesome video! Question: if you switch the order of data sources (put the original CSV file on the left and the date scaffold Excel on the right) with the same relationship settings, does it work the same way as your video? Does the primary vs secondary data source matter in relationships?
Thank u
I've done something similar where I've joined a date dimension to my main fact table though when I try to create avg measures to calculate avg # of exams per day, it's giving my an avg exams per day, but only for days in which exams were completed. This is useful but not what I was wanting. How do I get it to include those days in the avg calculation in which 0 exams were completed?
Hi, Tim. Need help, I have date scaffold and am using ifnull or zn function to display 0. However, Zero’s are not showing. What might be causing this
Tim - excellent video! What do I do when the only operator I have available is =? The >= or
After doing some research, it appears the issue may be that one of my data sources is using a Web Data Connector and that may be limiting what relationships I can do
hadn't seen your initial comment. I'll have a play to validate what you discovered. Typically a data source restriction would be the reason some items are limited.
@@dagie07 Have you found a way to deal with that even though only operator = is available ?
@@tzuriminecraft Not entirely...I'm trying to see if I can calculate what I need with a formula, in lieu of using the date scaffold
What should we do for data that does not have a end date? It says null on the x axis when i make the graph in tableau
This is data that is still active, it has a start date but no end date
you could write logic that uses today if it is null that way it constantly changing as the days progress or use a date in the future to have it extend beyond today and then mark dates beyond today in a different colour to show they're still being processed.
Can you please make a video on how to create six time periods out of a year similar to 4 quarters. Tableau's "date field" by default expands into year, 4 Qtrs and 12 months when you keep a date field in the shelf. Can I make the same field to display 6 time periods similar to 4Qtrs. This will help me with Pair over Pair similar to Quarter over quarter or month over month trends/ calcs. A pair of mths could be P1=Jan & Feb, p2=Mar & Apr like that until P6 =Nov & Dec. I need this "period field " exactly like a DATE FIELD with all its features. Say for ex when i keep this newly created field in the column shelf, it should roll out as year, Qtr, Set (Pair of mths), and then Day. With this I can do a period over period analysis like quarter over quarter. THANK YOU!!
Do they need to be equal periods or do they have set start and end dates? If the later you could just do a conditional calculation no need for a video, its just a logic calculation where you describe when the periods start and end or you could just manually group the months using the month date dimension.
Great stuff! I would love to use this solution, but our data is published to the tableau server. So we can't use relationships. Makes me very sad haha.
This is a huge feature request over with Tableau Devs. I think its more of a question of when than IF for that one.
Hi Tim,
I am not getting the right value after scaffolding when I try to put the count of days I get weired numbers
Check your relationship is correct. That’s the only explanation. Your data might be at a different level of granularity than you expect as well causing this. Check out my video on granularity as part of the LOD series
Do you use this with big live data sets? + Do you use this when there needs to be more than one date that relates to the date scaffold? (created date for pipeline generation, closed date for closed deals for example)
Yes it works with both as long as you mapping to a single date timeline. It’s very performant even at scale as the data model handles the complexity and only brings in the necessary rows for the view unlike a join which executes on the entire set of tables and doesn’t offer flexibility to change context.
Ps you can also flip this model on its head to answer questions in reverse. I.e. relate a dimensions many date multiple times to the same timeline allowing you have all the attributes at different points in time available at any point on the timeline.
@@TableauTim I am working on something along these lines right now, thinking about having the date calendar table as the base and then bringing the same "actuals" table onto it as just a = left join in the physical layer, to join create date, close date, and snapshot date. But then doing a relationship of that table out to two dimension tables and then target tables where we bring in the = method to bring in the targets.
I tested this out with the targets and it was performing correctly, but ran into performance issues with the physical join!!
So ( Date dimension = (Created date table, slimmed to required metrics) (close date table) (snapshot date table) ) ~ (Dimensions) ~ (= Targets)
!! 😅
I want to monitor data date like that but level Minute :(
Same logic works at the minute level but only if you have many records at the second level otherwise it’s pointless. Just use a date time field rather than date and the rest just works
Dataset???
It’s an example not a follow along tutorial. You can apply this idea to any dataset. The excel scaffold you’d need to create yourself and the other data set was mock data from mockeroo. You’d can create that for too in mockeroo.