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
      • 30. Re: Got a fun Educational Brain Teaser
        Adam Crahen

        Ok version 2 on the way.  I'm on to you #8.

         

        2016-02-24_19-43-23.png

        • 32. Re: Got a fun Educational Brain Teaser
          Aaron Clancy

          I tried the white line approach that  The specified item was not found. used earlier but you can tell that isn't how you did it because your lines are not cut off.

           

          Here's one using "pages" set to all with show history.

           

          Again, probably not how you did it but I can't figure out how to break the line without using an aggregated dimension.

          Screen Shot 2016-02-24 at 7.03.55 PM.png

          • 33. Re: Got a fun Educational Brain Teaser
            Steve Mayer

            Got it. No hard-coded grouping required to get rid of the connecting line, but man, that did take me awhile to figure out.

            Thanks for the excellent puzzler.

             

            • 34. Re: Got a fun Educational Brain Teaser
              Adam Crahen

              I think I've seen like 4 ways to do this on the thread.

               

              2016-02-24_20-48-41.png

              • 35. Re: Got a fun Educational Brain Teaser
                Amanjot Klair

                Now this is disappointing...

                 

                Can't get the lables to numbers..

                 

                Part1.PNG

                Part2.PNG

                • 36. Re: Got a fun Educational Brain Teaser
                  kettan

                  And I am quite sure that this excludes referential features such as:

                   

                  1) annotations - can't move text outside 'canvas' - that is, to axis

                  2) reference lines - can't show text on axis

                   

                  Source for an idea 

                  I see some value in having freedom of placing annotations and references lines outside 'canvas' - so they don't block what is shown in canvas, and also so the thing presented in this educational brainteaser can be done without adding data points to axis.

                  • 37. Re: Got a fun Educational Brain Teaser
                    kettan

                    Again, probably not how you did it but I can't figure out how to break the line without using an aggregated dimension.

                    It is difficult without a recipe!

                     

                    It is easy with Jonathan's recipe in  Display Missing Values and Incomplete Periods in Time Series  which deserves more likes than its 3 it got so far.

                     

                    Ps. Mentioned idea functions also as a resource page (links to other pages about the same topic) and therefore added this Educational Brain Teaser to its collection.

                    • 38. Re: Got a fun Educational Brain Teaser
                      Simon Runc

                      hi Rody,

                       

                      Thanks for the ping...yes nice little challenge here..definitely had me scratching my head!, and going through the repertoire of tricks! (...I'm was only on my first coffee of the day, by the time I'd got to coffee no. 3...I had it!...well 'a solution' anyway)...I give it a 4/5

                       

                      Really good brain-teaser...and really gets into some of the 'nuances' of Tableau!! - looking forward to the Rody solution!!

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

                        Thanks everyone for participating in this one!

                         

                        There was definitely a few "tricks", but they are important things to know about Tableau.

                         

                        So let's get started on the solution.

                         

                        The first problem we have to recognize is that the Requirement asks us to Pad out missing data, i.e. we need to have the axis show those missing values.

                         

                        Now there are a few ways to enable Domain Padding, but IMHO, the easiest is using "Show Missing Values", and a Table Calc, such as INDEX().

                         

                        So to begin, we need to convert our "Months Since String" to a date. Now there are many ways to do this, but I will highlight two methods I prefer in situation like this.

                         

                        1. The DATEADD Method.

                         

                        Since we know that "Months Since String" is really a NUMERIC value representing the MONTHS SINCE a specific date, we can use the DATEADD function to create a new date field in our Datasource.

                         

                         

                        DATEADD('month', INT([Months Since String]), #2015-01-01#)

                         

                        What we are doing here, is simply converting our STRING into INT, and then adding those months to a designated DATE (Which is our Start Date).

                         

                        Now the thing to remember from this trick is we can either Type out the DATE Explicitly, OR we can use a Parameter, either method will work.

                         

                        2. The DATEPARSE Trick

                         

                        For this particular situation, the Specific Values of the DATES don't really matter.....We just need a DATE field that follows the same chronological order of our STRING Field. So, knowing this, we can use a simple DATEPARSE function.

                         

                         

                        DATEPARSE('MM', [Months Since String])

                         

                        This follow a similar pattern line of logic as converting an INT to DATE, except DATEPARSE uses an EPOCH Date of JAN 2000.

                         

                        By using just 'MM' we are actually able to create this as a functioning DATE field. ALSO you could have MONTHS SINCE STRING = 36, and Tableau will actually carry this over in 12 month increments from the DATEPARSE Epoch DATE.

                         

                        Ok, so either method will create a DATE field that we need. So now we need to enable DOMAIN Padding.

                         

                        Simple way to do this is "Show Missing Values" and add an INDEX() calc to the DETAIL Shelf. This is what will BREAK our line for the Missing Values

                         

                         

                         

                        Awesome! Now we have the basic setup, BUT you don't want to show this as a DATE, you want to simulate the MONTHS SINCE field.

                         

                        So how do we do this? Well we just need to swap our DATE Field with our INDEX. And Set INDEX() to compute using the DATE Field we created

                         

                         

                        IF YOU LEARN ANYTHING LEARN THIS!!!!!!

                         

                        Tableau treats DATES very differently than any other field DATA TYPE in Tableau. When you are using the DROPDOWN to change the DATE to either a DATEPART or a DATETRUNC, Tableau is not creating a new FIELD, it is just modifying the properties of the DATE, BUT it is still retaining the CORE properties of that DATE FIELD.

                         

                        For example, when you use one of the DATEPART options, you will notice that TABLEAU is not actually changing the field to an INT. We can identify this by looking at the FORMATTING OPTIONS.

                         

                         

                        Why is this important? Because this is not the only PROPERTIES that Tableau retains!

                         

                        Let's repeat the process above, but start with NOT Showing Missing Values when the DATE is on the Columns Shelf

                         

                         

                        Notice when the DATE is on the DETAIL Shelf, there is not an OPTION to SHOW MISSING VALUES. So we are left with a VIZ that is not we we intended.

                         

                        The way to get around this is simple. Just move the DATE field to the Column/Row Shelf, Check SHOW MISSING VALUES, and the DRAG it back to the Detail Shelf.

                         

                         

                        Now Drag it back

                         

                         

                        Tableau RETAINS the SHOW MISSING VALUES property when you move it back to the DETAIL SHELF!!!!!!

                         

                        This is a nice trick to remember in the future!

                         

                        Now we are making progress, BUT our X Axis is still not correct. INDEX() in Tableau, unlike in programming, starts at 1, but we need it to start at 0. What do we do? Simple, just subtract 1 from the INDEX()

                         

                         

                        Perfect, We are Almost home!

                         

                        So the last thing to do is add "MONTHS SINCE JAN-15" to the X-AXIS.

                         

                        To do this, we just need to do a FORMATTING Trick.

                         

                        Right Click on the X-AXIS and Select Format.

                         

                         

                        Go to Number (Custom) first. This will make your life easier

                         

                         

                        After you do this, Click CUSTOM, Tableau will bring over the Formatting Properties of Number (Custom). Another Time Saving Trick!

                         

                         

                        Now for the real trick!

                         

                        DELETE Everything after the first semi colon. Because of the formatting, and ANY Negative Number will be treated differently than Positive numbers. And Really, you can put anything you want after the Semi-colon

                         

                         

                         

                        Once you have that. It;s all about cleanup and you're done!

                         

                         

                        Once you have this down, it actually only takes about 5 Minutes to do. You just have to remember some of the Nuances in Tableau, and how to "Trick" it into doing what you want.

                         

                        I hope this was a fun Brain Teaser!

                         

                        Thanks everyone!

                         

                        Regards,

                        Rody

                        9 of 9 people found this helpful
                        • 40. Re: Got a fun Educational Brain Teaser
                          Simon Runc

                          Thanks for this, and great explanation. Although not quite the same arrangement, I had all the same elements (using index() in the Columns and not on Detail would have made things easier!!)

                           

                           

                           

                          Great point you make on the Date though...I didn't really appreciate this subtle differences in the handing of 'real' dates and the same function as a 'pure' formula.

                           

                          So if I use the DATEPART from Date

                          double click the pill to see the formula

                          it shows me that it using

                           

                          DATEPART('month', [Date - Based on Months Since])

                           

                          However if I use this in a formula

                          and try and use this field, it is no longer 'Range Aware' ('Range Aware' is a term, I really like [I think Joe Mako/Jonathan Drummey came up with it], and are Pills that are aware of their Max and Min and Increment - basically Dates and Bins)...and so I loose my densification. For those who want to delve into the 'murky' world of data densification and range aware pills...here is a great video Tableau Request Live - Data Densification on Vimeo

                           

                          Great challenge and learnings...thanks again!

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

                            YESSS!

                             

                            I love that Tableau lets you Modify the Calc in the Canvas, but for DATES, it is a little miss-leading.

                             

                            You know me, I LOVE Dates in Tableau. It is so interesting the way Tableau treats Dates, and what we can do with them.

                             

                            But, when the release came out that allowed us to see the "Formula" underneath, I became very confused, for the same reasons you described.

                             

                            What's really interesting is CUSTOM DATES. Tableau LEADS you into believing that the field is NUMERIC

                             

                             

                            BUT it is not exactly numeric.......It retains properties of a DATE (To some extent).

                             

                            You can see this by simply looking at the AXIS, OR checking out the formatting....

                             

                             

                            There is something DIFFERENT that Tableau is doing here, and you can see it in the XML.

                             

                            I wish these types of things were more apparent. I know for me, it took some time to grasp what was happening.

                             

                            Sorry, tangent!

                             

                            Rody

                            3 of 3 people found this helpful
                            • 42. Re: Got a fun Educational Brain Teaser
                              Adam Crahen

                              Here are my solutions.  I didn't do anything with dates, I just made the string an INT.

                               

                              On the discrete version, I utilized a bin and the row grand total (to left - thx 9.2) and a calc to null the grand total.  For the axis label, I just edited the grand total text.

                               

                              On the continuous version, I used a dual axis and two sets to break the line.  Then, I sat there for 45 minutes scratching my head before I remembered I could format that negative number!

                               

                              Fun one Rody!  9.2 workbook.

                              2 of 2 people found this helpful
                              • 43. Re: Got a fun Educational Brain Teaser
                                Jeff Strauss

                                thanks for the thorough explain!  It's easy to forget about all the available functions such as INDEX() when you don't use it regularly.

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

                                  Hey Adam,

                                   

                                  Thanks for sharing your approaches.

                                   

                                  I have actually used BINS in the past to do this same thing, and it works great for Domain Padding. And the "Tricky" things we can do with Grand Totals now is awesome (Like create a "fake" dim of "Total members" to the top of a Funnel)

                                   

                                  I decided to use DATES here, because I wanted to highlight a few of the nuances of them, and how we can Retain "Show Missing Values" when we drag the the DATE on the Details Shelf.

                                   

                                  Def great to see other ways of doing this. Just goes to show that there is more than one way to skin a cat in Tableau.

                                   

                                  Best regards,

                                  Rody