1 2 3 4 5 69 Replies Latest reply on Feb 26, 2016 9:28 AM by Łukasz Majewski Go to original post
• 45. Re: Got a fun Educational Brain Teaser

Hey Rody-

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.

• 46. Re: Got a fun Educational Brain Teaser

Exactly!

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!

Best regards,

Rody

• 47. Re: Got a fun Educational Brain Teaser

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

Look familiar?

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.

Regards,

Rody

• 48. Re: Got a fun Educational Brain Teaser

hmmm...interesting!

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!!

• 49. Re: Got a fun Educational Brain Teaser

Yes exactly.

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.

Regards,

Rody

• 50. Re: Got a fun Educational Brain Teaser

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.

-Steve

1 of 1 people found this helpful
• 51. Re: Got a fun Educational Brain Teaser

This is pretty good!

Question though....

Is this Actually a DATE FIELD? I am assuming it is....if so I'm wondering how you got the Text on the Axis..

Cause, I seem to be stuck!

Will try to figure it out, but interested in seeing what you did here....

Regards,

Rody

• 52. Re: Got a fun Educational Brain Teaser

"Date" is indeed a calculated field I created of type "Date & Time".

-Steve

• 53. Re: Got a fun Educational Brain Teaser

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

Regards,

Rody

• 54. Re: Got a fun Educational Brain Teaser

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:

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.

-Steve

4 of 4 people found this helpful
• 55. Re: Got a fun Educational Brain Teaser

DANG! I was so close! Lol I had the same idea with using the EPOCH Date, but couldn't quite figure out the formatting!

But, this is a good one to know!

Thanks for sharing. Did you know this? Or was it a happy mistake?

Most of my tricks come from "Happy Mistakes" lol

Thanks again!

Rody

• 56. Re: Got a fun Educational Brain Teaser

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.

-S

• 57. Re: Got a fun Educational Brain Teaser

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.

Regards,

Rody

• 58. Re: Got a fun Educational Brain Teaser

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!

Rody

• 59. Re: Got a fun Educational Brain Teaser

I got stuck in my attempt to connect dots and finally got a solution.

Now can you build this with the same data?

1 2 3 4 5