1 2 Previous Next 17 Replies Latest reply on Apr 1, 2012 12:50 PM by Joe Mako

    Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did

    Zach Leber

      I made a workbook in Tableau 6 that uses table calculations to find times since previous events.  This worked even with sparse data, i.e. missing events, because LOOKUP would find the next row with non-Null data.  But in Tableau 7 this behavior seems to have changed, as it will not skip over rows, so will return Null as the time.  You could argue that Tableau 7 is doing the right thing, but I am relying on Tableau 6's behavior.  I've attached screenshots from Tableau 6.1 and 7 and a packaged workbook that will open in 6.1 or 7 so you can see the difference yourself.   Any ideas?

      Tableau 6 lookup.pngTableau 7 lookup.png

        • 1. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
          Jonathan Drummey

          Hi Zach,

           

          I believe what you're running into is that Tableau changed how it pads data between 6.1 and 7.0. I don't think LOOKUP changed behavior at all. If you look at the Times Since Previous Event view, in Tableau 6.1 you see 12 marks and Tableau 7.0 says there are 16 marks. Those 4 extra marks are all changing the LOOKUP and making the calculations different.

           

          Tableau 6.1 only pads data (i.e. generates a cross product of sorts) when you have dimensions on both the Rows and Columns shelves. Tableau 7.0 pads more often, for example when you have a dimension on the Colors

          shelf. Joe Mako recently showed me this new behavior, it can be frustrating because there's no user control over it.

           

          You can see the padding in action in if you drag Event Type onto the Rows Shelf in the Times Since Previous Event chart. In Tableau 7.0, this has no effect on the individual lines, since the data has already been padded. In Tableau 6.1, the view changes from the view you've been getting to exactly what's in the 7.0 view - since you now have Sample on Columns and Event Type on Rows, Tableau pads out the data and you have 16 marks.

           

          So now that I have an explanation of what's going on, I unfortunately don't have an answer of how to get this to work for you. I've been trying to come up with something, but I'm stumped.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
            Richard Leeke

            Yes, that's exactly what's happening.

             

            I feel it should be possible to work around it with careful use of PREVIOUS_VALUE(). I had a quick play, but didn't quite get there and I need to get to work. I think I got all the numbers right but they were offset by one row!

            1 of 1 people found this helpful
            • 3. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
              Zach Leber

              Thank you both for engaging this issue, this was my first extensive use of table calculations, and I'm challenging them with sparse and duplicate data to reflect the reality of our use case (tracking sample processing times in the face of missing and repeat events).  I briefly tried PREVIOUS_VALUE(), thinking it may get past the padding (which I assumed to be NULLs at the time), but it seemed to go in the wrong direction (I wish there were a NEXT_VALUE()), and not skip anyway.   I'd love your help.

              • 4. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                Richard Leeke

                Yes, it does go in the wrong direction for what you want, so I had to invert your manual sort order to get anywhere at all!

                 

                A request for a NEXT_VALUE() function was one of my bits of feedback on the Tableau 6 beta, I recall.  ;-)

                 

                Will have another look over the weekend (it's already Friday where I am, so that's not as far away as it sounds...).

                • 5. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                  Richard Leeke

                  Lunchtime - didn't wait for the weekend.

                   

                  I got the approach I mentioned before going. It's a bit ugly, but it's all I can think of immediately.

                   

                  Sing out if you want anything explained...

                  • 7. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                    Zach Leber

                    Wait until you see what I just discovered.  First, thank you Richard.  When I had experimented before with PREVIOUS_VALUE() I didn't appreciate the subtlety that it could be used to provide recursion.  That is powerful and a little mind-bending.  Second, because I didn't want to change my event order for other reasons, I implemented a less sophisticated version of Richard''s algorithm using IFNULL(LOOKUP(SUM([Event Time]),1), LOOKUP(SUM([Event Time]),2)) which can be extended as necessary.  But third, I stumbled across an elegant if accidental workaround.  I'm using a line graph to plot this data, and at some point it became discontinuous because of Nulls, so I dragged the column dimension onto the Path shelf to get the line to stay connected.  This turns out to be the antidote to the padded row problem, and suddenly my original simple formula works again in both 6.1 and 7.0.  I've attached that workbook (Sample Event Test). 

                    Sample Event Test 61c.png

                    I've attached my original project that extends these calculations to second level in order to get averages across sample sets.  I have two even more subtle problems with that workbook (Sample Event Times) but they will really take some study and trickery I believe, and may require a third level of aggregation that is not possible without a modified data source.  This data has repeat events in addition to missing ones, hence my use of MIN([Event Time]) within each event type. For each sample I only care about the first event of each type.

                    1. In the "LCSET event times" view, I'd like to have the "Average since previous" measure be a line graph but I can't get the dots to connect. 

                    2. In the "LCSET total times" view, I'm trying to get an average across sets rather than samples.  There are 3 samples across 2 sets that have completed with times of 6,11, and 7 so their average is 8.0, but set 1's average is 8.5 and set 2's average is 7.0, so the set average is 7.75. But because sample is on the LoD shelf to get the calculation correct, it weights the average of my reference line.

                    Sample Event Times 61c.png

                    • 8. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                      Richard Leeke

                      I'll have a proper read through this later and see if I can get my head around it.

                       

                      In the meantime, I'll just mention a thought I had after posting my workaround, which is that I'm pretty sure you can use my method without changing the display order of your original (I suspected that you wouldn't want it reversed).

                       

                      I think you just need to add the required sort order to your data source (you could either have an event order lookup table or you could just rename your event types as "1 - FC", "2 - QTP", etc). That would allow you to sort by that order ascending for display and descending in the advanced partitioning dialog of the table calculation.

                       

                      I haven't tried this, but I think it will work.

                       

                      I also haven't read your latest post thoroughly enough to know whether that would help - it could be that the Path shelf discovery makes that irrelevant, though at first glance I think the power of PREVIOUS_VALUE() may be useful to you to cope with an indefinite number of duplicates, etc.

                      • 9. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                        Richard Leeke

                        I've attached a modified version of my previous workbook showing how to maintain the display sorted as you want it whilst sorting the table calculation how PREVIOUS_VALUE() needs it. I just added a ort order calculated field.

                         

                        The use of the Path shelf does seem to make that unnecessary, though. That's an interesting discovery. You can see very clearly that it is stopping Tableau from padding the domain by watching the count of marks in the status bar. Dragging Sample onto Path reduces the mark count.

                         

                        I've had a bit of a think about your next two issues.

                         

                        I can't figure out how to join the dots for your first question - but the use of the Path shelf has always been a black art for me. Your best hope is that Joe Mako sees this - Joe has helped me out with joining the dots in line charts countless times - I just seem to have a mental block with it.

                         

                        The average of averages just needs some nested table calculations with careful use of partitioning. Note that for the average by Set the calculation only returns a value for the first row and NULL for any others, otherwise in your example you would get (8.5 + 8.5 + 7) / 3 instead of (8.5 + 7) / 2.

                         

                        I've attached an updated copy of your workbook (saved in v7) showing that.

                        • 10. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                          Joe Mako

                          In sort:

                          For your exact situation here, the option I would prefer is an Advanced compute using, both dimensions on the Compute using side, sorted with "Sample" on the top, and then Restarting every set to "Sample". Additionally, because you have a manual sort applied to your "Event Type", you would need to create another calc field so you can inform the Advanced dialog of the sorting it needs. Then you can adjust the pane formatting of your pill so it is Hide (Connect Lines).

                           

                          Attached is this option in Tableau version 7.

                           

                          Of course, as with many situations in Tableau there are multiple ways to achieve the same results.

                           

                          In long:

                          I am still trying to figure out all the differences between 6 and 7 myself, and the following may not be completely accurate, this is just my observation from trial and error, so it is subject to change as I learn more. To get exact details on the logic of how table calculations are evaluated, I hope Tableau one day provides proper documentation.

                           

                          A Very incomplete list key concepts to be aware of when dealing with table calculations (just some of the ones that apply to this situation):

                          1. Be aware of what pills are dimensions (non-aggregated, discrete/blue or continuous/green)

                          2. Be aware of the effects of the different combinations of dimension pills on different Cards, some combos:

                          - Marks and Rows or Marks and Columns

                          - Rows and Columns

                          - Rows and Columns and Marks

                          3. Be aware of the four ways to set your partitioning/addressing:

                          - Layout based (Table down, Pane Across, etc)

                          - Pill based (selecting a single dimension from the Compute using)

                          - Advanced (use when you need to address on multiple dimensions)

                          - Complex custom calc with Layout and "Show Me" (for getting results that cannot be achieved with Tableau's user interface)

                          4. Be aware of the combination of different pill arangments and different partitioning/addressing settings

                          - every combination of points 2 and 3 above can have different effects, depending on your exact situation

                          5. If every possible combination of each distinct dimension value does not exist in your data source (that table calculations have access to, after normal filtering and before table calc are evaluated), then you need to be prepared for padding. (this is an extension of points 2, 3, and 4, these combinations determine how Tableau will pad, and yes this logic changed from version 6 to 7)

                          6. Be aware of your mark type (this is new to version 7)

                          - Line and Polygon marks types behave differently than other mark types (extra padding based on mark type and pill arrangement, not just for table calcs)

                          - Area and Filled map are another combo (but I have not done full testing, so I am not sure how it is different yet)

                           

                          Additional factors that effect table calculations, good things to be aware of, and could become a factor as you develop this worksheet (as you add more pills). This is not a complete list.

                           

                          7. Be aware that a table calculation pill on just the Filter shelf can only address/partition on pills on the Rows and Columns shelves. Use ctrl-drag-and-drop with the pill so it is also on the Marks card (eg Level of Detail shelf) to enable a table calc pill on the filter shelf to see dimensions on the Marks card shelves. This is very important when dealing with Multiple mark types, aka combo charts, when you have multiple Marks cards.

                          8. Be aware a cross-tab layout can pad your data (discrete dimension pills on both the Rows and Columns shelves, see point 2)

                          9. Be aware Tableau 7 introduced new ways to pad your data

                          - when the compute using for your table calc is effectively a discrete pill that is a date data type (only when it is effectively the only pill being addressed on)

                          - sometimes when you use a Set (I am still not sure on this one, still testing)

                          10. Be aware of how Subtotals and Grand Total work (related to point 2)

                          11. Be aware of how reference lines work

                          12. Be aware of how different table calculations are evaluated (TOTAL() is a separate query, PREVIOUS_VALUE() is self-referring, etc)

                          13. Be aware of the order of operations for filtering

                           

                          I am sure there are more factors, I have not gone into great detail here, and I am sure there are others that I just am not thinking of currently, or have not discovered them yet. I'll edit this post as needed.

                           

                          Anyone going to the Tom Brown lunch session on Tuesday at the TCCEU12? I would be interested if his "works every time" https://twitter.com/#!/_tombrown_/status/186132156391751680 method takes these factors into consideration. If not, I would be glad to provide examples of how different situations require different methods, and some methods are more ideal or flexible than others. Sometimes a different method makes it easier to get the desired results.

                           

                          In my opinion, the documentation Tableau currently provides on table calculations is inaccurate, misleading or simply non existent.

                           

                          In case it is not clear, my goal is to offer constructive criticism, I love the software, and I truly want to help, I am just sad with the current documentation. Please let me know if I can help in any way.

                          • 11. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                            Joe Mako

                            I am not able to wrap my mind around your request for a NEXT_VALUE() in the context of what PREVIOUS_VALUE() does. My initial thought is you are are simply looking to reverse the sort order, but I am not sure.

                             

                            Can you provide an example use case situation for your concept of what NEXT_VALUE() would do?

                            • 12. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                              Richard Leeke

                              You're quite right, there's no real need for a NEXT_VALUE(). The reason we were struggling was that with a manual sort the "automatic" sort order works fine in table calcs but you can't reverse that. That's why I added a sort order calculated field in my latest version above - that way the view can be sorted either manually or on the sort order column and the table calculation can sort on the sort order column descending.

                              • 13. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                                Richard Leeke

                                You may not get much chance to post on the forums these days Joe - but you're still a mile ahead of the rest of us on this.

                                 

                                Thanks for posting all this - it's great to have the collected learnings all together in one place like this.  Seeing all of these points together like this does highlight why table calculations are so difficult.

                                • 14. Re: Tableau 7's LOOKUP() function doesn't skip Null data rows like Tableau 6 did
                                  Joe Mako

                                  For point one, you need a continuous axis for your your columns in order for Tableau to connect the lines. I know this is not ideal, the other option is to perform some of these calculations prior to Tableau (preparing data for Tableau can help reduce the brain damage from trying to get Tableau to do exactly what you want). Another options is custom SQL that performs a self join, or some combination of a data blend, really there are a lot of options.

                                   

                                  I changed the partitioning setup and included a couple of intermediate table vies that I used to set the computations.

                                   

                                  see attached for the additional sheets added in version 6, and the method used will also work in version 7.

                                   

                                  Here is another thread with more details on the method I used here:

                                  http://community.tableau.com/message/136628#136628

                                   

                                  For your point two, I was not able to understand what you are looking for. You will need to provide additional details on the exact business logic you want applied, or at least of a mock up of what you expect for a result for this sample data, because currently your explanation is not clear to me.

                                   

                                  Message was edited by: Joe Mako, added link to more details

                                  1 2 Previous Next