1 2 3 4 5 Previous Next 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
        Adam Crahen

        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.

         

        Adam

        • 46. Re: Got a fun Educational Brain Teaser
          Rody Zakovich

          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
            Rody Zakovich

            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

             

            Step 1.png

             

            Now let's explicitly write out the DATEPART calc directly in the canvas, from this field....

             

            Step 2.png

             

            Now watch what happens!

             

            Step 3.png

             

            That's not a NUMERIC Field! That is doing the same thing as the Dropdown......

             

            And we can actually see this in the Dropdown.

             

            Step 5 But step 4.png

             

            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

             

            Step 4.png

             

            So, let's do the same thing, but without first dragging the DATE Field onto the Row/Column Shelf

             

            Step 6.png

             

            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
              Simon Runc

              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
                Rody Zakovich

                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
                  Steve Mayer

                  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
                    Rody Zakovich

                    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
                      Steve Mayer

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

                       

                      -Steve

                      • 53. Re: Got a fun Educational Brain Teaser
                        Rody Zakovich

                        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
                          Steve Mayer

                          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.

                           

                          -Steve

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

                            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
                              Steve Mayer

                              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
                                Rody Zakovich

                                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
                                  Rody Zakovich

                                  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
                                    Łukasz Majewski

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

                                    Now can you build this with the same data?