I appreciate your time putting that together. I definitely learned some new date tricks and your explanation was great! I had an index attempt at one point and couldn't make it work so you connected the dots for me (literally!) I could see a bunch of different approaches yesterday, it is cool to see how many different ways people thought of using.
And really, that's the whole point of Educational Brain Teasers (IMHO).
There are a lot of ways to do "Out of the Box" things in Tableau, and (usually) a lots of peoples' approaches will be different. But what is great about that it gives us a chance to learn from each other. It gives us a chance to see/and learn how Tableau operates in certain situations, given Data Types and Pill Arrangements.
For example, I didn't even think of using the Pages Shelf like Aaron described. Now I have another weapon in the bag.
I just hope everyone learned "Something" and didn't spend an hour just banging their heads!
Wanted to add this in, as it deals with some of the nuances of Dates and the Canvas Calcs described in this thread.
Let's drag Order Date on the Column Shelf and Choose Exact Date
Now let's explicitly write out the DATEPART calc directly in the canvas, from this field....
Now watch what happens!
That's not a NUMERIC Field! That is doing the same thing as the Dropdown......
And we can actually see this in the Dropdown.
Here is the real kicker, even though we "Created a calc" in the canvas, to Tableau, it is still the same field! So we can't drag it to the Dimensions Pane
So, let's do the same thing, but without first dragging the DATE Field onto the Row/Column Shelf
Now look what happens
Same thing happens.
From what I can tell, when creating Row Level Date Calcs like DATEPART, OR DATETRUNC....OR regular aggregates on Measures ON THE CANVAS...Tableau isn't actually creating another Field/Calculated Field, it is just taking the step you would in if you actually dragged the Date or Measure onto the Canvas, and changed the Date Properties, or the Regular Aggregation Type (For Measures).
Don't know if this helps anyone, but I thought it would be good to share.
And if we create the calculation, using the traditional editor
either DATEPART('month', [Order Date] or DATENAME('month',[Order Date])
and drag them into the Canvas they are now either Integers or Strings, with no (to my quick check anyway) remnants that they were derived from dates!!
The difference is where the calculation is created, whether it be On-Canvas or Off-Canvas, AND the type of calculation you are creating (Important to note on that).
If the calculation is created Off-Canvas, Tableau will create a New Field in your Datasource, this field has its own properties, and is not tied to field the calculations was created on.
If the calculation was created On-Canvas, and it is a Calculation that is native to the Dropdowns (Not Window Calcs, LoDs, etc) then Tableau is not creating a new Field, it is merely making changes to the existing Field, thus it will block you from dragging it do Measures/Dimensions Pane as a new field/calc
The Canvas plays a much more important role than you might think. But, this is who we can trick Tableau too, depending on the situation.
1 of 1 people found this helpful
Challenge back to you Rody Zakovich (and any other takers)... can you solve it with just two pills (see below)? My pill labels might be giving away the solution, but... it requires one more wacky trick.
Thanks again. Really enjoyed this one & learned a ton from this.
"Date" is indeed a calculated field I created of type "Date & Time".
Well....you have me stumped!
Which means there is a formatting trick i don't know!
Please share how you did this, I'm very very curious lol
4 of 4 people found this helpful
Turns out you can format a date using numeric formatting like #,##0 and it will display as the number of days since 1/1/1900. Tableau must store their dates internally as a decimal value of the number of days since 1/1/1900. I tested this with a calculated field with the formula NOW() and formatted it using the Custom date format: ##,0.00000000. You get:
Since the days start going negative at 1900, I solved your puzzle with 2 pills using the calculated field:
DATEADD('day', INT([Months Since String]), #12/30/1899#)
then used the Custom date format:
#,##0; Wacky Trick
There will probably never be a use case for this again but it has certainly deepened my understanding of Tableau.
It was a "happy mistake" while pulling my hair out trying to get a date to display as a negative number on the x-axis.
There is perhaps one Use Case for it, though there are easier ways to do this....
But, it is interesting that we can do it this way
Let's say you wanted to show all DAYS in a YEAR, but the actual day in the year, i.e., 1-365
Using a variation of this method, you could created a DATEDIFF from EPOCH, and use custom formatting....
Which will start a Convert your Dates to Start from the Epoch Date....
Then you can use the same formatting trick....
Like I said, easier ways to do this. But this is kinda cool.
Easier way to do this is simply using a DATEDIFF with the LoD
The advantage of the Method above, is that it is still technically a DATE field, even though it doesn't look like it. So we get the benefit of Domain Padding (If we need it).
That being said....We can also just create a BIN from our DATEDIFF, and Get DOMAIN PADDING as well.
Maybe there isn't a use case for it!