10 Replies Latest reply on Mar 2, 2017 2:51 PM by Joe Oppelt

    Table calc filter messing with running_sum calc

    Andy Holt
    Andy Holt Mar 2, 2017 12:36 PM

      OK, so first-up I'll preface this question by saying that I don't *think* it is possible to achieve what I need, but I am seeking a second opinion and would love to be proved wrong!

      I am thinking that the only way to achieve what I need is by having my DB developer build me a custom view of the data in the form that I need, rather than trying to allow Tableau to do the heavy lifting.

      But as I say, please tell me I'm wrong

       

      The problem overview is that I am attempting to create a line chart of a running_sum of a series. The running_sum needs to start at the beginning of the calendar year, but the chart only wants to show a specified month.

      I'll talk you through my attached workbook where I've attempted to demonstrate my work-flow so far.

       

      [data exploration]

      This just shows the data complete with 'holes' and a simple Running Total Quick Table Calculation. By click 'Showing Missing Values' on the Transaction Date field it has padded my data field.

       

      [zeroes for nulls]

      By utilising a Lookup table calc I am able to fill the null values in the series with zeroes, and calculate a running sum across that new series.

       

      [chart version]

      I wanted to check that this would still work for a line chart, and by changing the Transaction Date to a continuous dimension, this works fine. Zeroes for nulls in the top chart, and all dates shown in the cumulative chart at the bottom.

      [Note, the final value of 1,485)

       

      [w/ table calc filter]

      Now I want to only show the chart for February. Obviously I cannot use a simple filter on the Transaction Date field because then it will mess up my running_sum calculation, so I need to think about the Tableau order of operations and use a Table Calc Filter so that it filters *after* doing the calculations.

      Note the final value of 1,485 is still there, which is good. However something has clearly gone awry.

       

      [Chart comparison]

      The problem is more evident here. You can see that where we had the zeroes in the series allowing 'flat bits' in the cumulative chart (on the left) the use of the table calc filter has messed this up and now only dates with transactions are populated (on the right).

      All dates are shown (due to using the continuous Transaction Date) but not all dates have values.

       

      So there you have it. Have I missed a trick somewhere or am I correct in thinking that this problem is not solvable given my current data structure?

       

      Thanks a lot

       

      Andy

       

      (Tableau v10.1.3 btw)

        • 1. Re: Table calc filter messing with running_sum calc
          Joe Oppelt
          Joe Oppelt Ambassador Mar 2, 2017 12:43 PM (in response to Andy Holt)

          Your LOOKUP filter is finding only actual rows with a date that fits the criterion.

           

          Change your filter to grab both TRUE and NULL and you'll match your Chart-Version viz

          • 2. Re: Table calc filter messing with running_sum calc
            Andy Holt
            Andy Holt Mar 2, 2017 12:52 PM (in response to Joe Oppelt)

            Hi Joe

            Many thanks for taking the time to reply.

            However that doesn't quite work. When I make the change you suggest my charts now begin at 14th January (i.e. the first date with a null value), rather than beginning at 1st Feb as required.

            Best rgds

            Andy

            • 3. Re: Table calc filter messing with running_sum calc
              Joe Oppelt
              Joe Oppelt Ambassador Mar 2, 2017 1:32 PM (in response to Andy Holt)

              Well this is quite a quandary...

               

              The ZN has to be on the outside of the whole mess to get the middling values to become zero.  Otherwise only the values that actually exist get the ZN treatment.

               

              Check out [ZN lookup sum trans amt (copy)].  If I ZN the whole thing, I can replicate your chart shape from Feb 1 forward, but we still get all the 0 values for the earlier dates.  But edit that calc and try to move the ZN inside the IF statement.  You'll see that only the existing rows get evaluated.  And yes, it's because Tableau only works on actual rows in the table.

               

              For kicks I tried "blanking out" the beginning of the line with colors.  (See sheet 5).

               

              I think you're going to have to pad out all the missing dates.  Either supply a minimal data source that contains all the dates, blend (or join) on that, and force all the dates to exist.  Or in your ETL where you are generating this data, pad out dummy rows for all the dates.

              1 of 1 people found this helpful
              • 4. Re: Table calc filter messing with running_sum calc
                Andy Holt
                Andy Holt Mar 2, 2017 1:44 PM (in response to Joe Oppelt)

                Thanks again, Joe.

                 

                I like your lateral thinking of colouring the line based on whether it is in our selected range or not. The problem though, is that this is only a snapshot of the data. The true data goes back to the beginning of 2011, which would mean an awful lot of blank space if I adopted this solution.

                 

                Within my data source I have a dim_date table that does contain all dates. I linked this to my fact table using an outer join thinking that this would force all dates to exist. Clearly not...

                • 5. Re: Table calc filter messing with running_sum calc
                  Joe Oppelt
                  Joe Oppelt Ambassador Mar 2, 2017 1:49 PM (in response to Andy Holt)

                  Man, you have all the same table names that we have here.

                   

                  Are you a non-profit too? 

                   

                  Here is a thought.  Take your dim_date and just make a running table of all the date values.  Add that to your workbook and make it your primary data source.  Then when you do SUM(secondary.TransactionAmount), you will either get your value, or null.  But then you will have an actual NULL value, not one you created with LOOKUP.  You can ZN that and LOOKUP in your filter...  The date will always be there.

                  1 of 1 people found this helpful
                  • 6. Re: Table calc filter messing with running_sum calc
                    Andy Holt
                    Andy Holt Mar 2, 2017 2:20 PM (in response to Joe Oppelt)

                    OK, close but no cigar! Haha.

                     

                    So your thought above of using the dates as the primary data source works perfectly for my snapshot data set.

                    However (and there is always a however, right?) the complicating factor is that this is only a snapshot data set.

                    There are other fields in what is now the secondary data source that I want to use for filtering, such as location.

                    I tried adding a second location into my data source and using that as a filter - but I am presented with the options Null, Loc1, Loc2.

                    To get the charts to work I need to include the nulls as well as the selected location.

                    Which I am not sure is a workable solution to roll-out to users. It just seems kinda cumbersome to have to explain to people that they need to select the location they want *and* not to deselect the null.

                     

                    I've attached a new workbook to show you where I've got to.

                     

                    And no, not a non-profit. Just best-practise DWH design?

                    • 7. Re: Table calc filter messing with running_sum calc
                      Joe Oppelt
                      Joe Oppelt Ambassador Mar 2, 2017 2:36 PM (in response to Andy Holt)

                      Will your filters in the secondary source be multi-select?  If not, then you can use a parameter instead of a filter.  You can even give ALL as a parameter value.  (So ALL or one value.) 

                       

                      Then in the secondary source you could make a data source filter that looks like this:

                       

                      [Location Param] = "ALL" or [Location Param] = [Location]

                       

                      But if you need multi-value selection for Location ...  not so easy.

                      1 of 1 people found this helpful
                      • 8. Re: Table calc filter messing with running_sum calc
                        Joe Oppelt
                        Joe Oppelt Ambassador Mar 2, 2017 2:36 PM (in response to Joe Oppelt)

                        that filter would go on the secondary source.

                        • 9. Re: Table calc filter messing with running_sum calc
                          Andy Holt
                          Andy Holt Mar 2, 2017 2:48 PM (in response to Joe Oppelt)

                          It wouldn't be multi-select, no.

                          But using parameters, of course, would necessitate manual amending it when a new location comes on stream, right?

                          • 10. Re: Table calc filter messing with running_sum calc
                            Joe Oppelt
                            Joe Oppelt Ambassador Mar 2, 2017 2:51 PM (in response to Andy Holt)

                            Unfortunately, yes.

                             

                            Some day Tableau will give us something like that in the Parameter space.  Under the larger umbrella of dynamic parameters, one of the categories is the capability to keep a parameter list in sync with data automatically.