12 Replies Latest reply on Jan 24, 2017 2:44 PM by Cindy Easton

    Add a Quick Table Calculation Filter

    Cindy Easton

      Hoping this can be done without posting my file...

       

      I've created all of my files to put together my Dashboard and as I started dropping sheets in I realized I have one chart that is the same just calculated differently.   I thought I'll just make this one chart and add a filter so they can toggle between views.  Well my "I'll Just" is turning out to be a little more difficult so I'm seeking help.  This will probably be easy-peasy for some of you well seasoned tableau users.

       

      Sheet 1 -  2017 Hires to Plan w/ tick marks showing 2016 hires by month

      Sheet 2 -  2017 Hires to Plan w/ tick marks showing 2016 hires by month (cumulative)

      Need - Dashboard that has one chart where they can filter between month total and running total

       

      Instead of having a chart for each I'd like for them to toggle between the two.    I've used the quick table calculations for the cumulative and even created formulas for the running total but can't seem to create a filter to toggle between the two.

       

      Hope that helps out.

       

      Using version 10.0

        • 1. Re: Add a Quick Table Calculation Filter
          Simon Runc

          hi Cindy,

           

          hope this is what you are after...

           

          I've first created a parameter to switch between the 2 and my formula is

           

          IF [Actual or Cumulative] = 1 THEN SUM([Sales])

          ELSE RUNNING_SUM(SUM([Sales]))

          END

           

          Let me know if this doesn't make sense, or doesn't do what you need.

          • 2. Re: Add a Quick Table Calculation Filter
            Jamieson Christian

            Cindy,

             

            Normally, something like this could be done by using a parameter to select which metric to show, and having a calculated field that populates with one metric or the other based on the parameter. However, if your metrics rely on table calculations, you may run into trouble. Mixing table-calcs with non-table-calcs, or (worse yet) mixing table calcs that rely on two different "Compute Along" configurations, will likely yield unexpected and undesirable results. Or, you may find that Tableau does not allow you to build the calculation at all.

             

            Without seeing a packaged workbook, I would say your best bet is to do the two different views and use a parameter to conditionally show them on the dashboard. See this knowledge base article about the technique: Create a View (Sheet) Selector for Your Dashboard

             

            If you would like to attach a packaged workbook, the community can take a closer look and see if there are other options for your specific scenario.

            • 3. Re: Add a Quick Table Calculation Filter
              Simon Runc

              hi Jamieson,

               

              So Tableau does let you have both a non-Table Calc and Table Calc as outputs to a condition statement (if you look at my attached workbook), but yes Sheet Swapping would work just as well (you'd take up an extra 3px...but we can live with that!).

               

              Although not asked here, when you want different "compute using"...there are a couple of options. One is to "nest" the table calcs (so create the 2 table calcs as separate fields), which then lets you set the "compute using" differently for both. The other is to create a dimension off the parameter switch too....say something like

               

              IF [Parameter] = "Category Running Sum" Then [Category]

              ELSEIF [Parameter] = "SubCategory Running Sum" Then [SubCategory]

              END

               

              btw this is hammed up example which makes no sense, but just to show the basic idea!

               

              and then use this in your Viz as the compute using Dimension...this way the compute using changes when the parameter does...you can get quite funky with this! I have a model where the user can select is something is compared to the Global Average or the Regional Average...with the dimension formula like

               

              IF [Parameter] = "Global" then "1"

              ELSE [Region]

              END

               

              The WINDOW_AVG then computes using this dimension...when it's Global all rows are the same ("1"), so there are no extra partitions (so the average is over global), but for the other option, the WINDOW_AVG is run over the Regions....

               

              Always lots of ways to do things in Tableau!

              • 4. Re: Add a Quick Table Calculation Filter
                Cindy Easton

                Simon - this is great.   But I have three numbers I'm trying to toggle between. 

                 

                Running Total of 2017, Running Total of 2016 & Running Total of 2017 hiring goal where the 2017 hiring goal is a different data source.

                • 5. Re: Add a Quick Table Calculation Filter
                  Simon Runc

                  hi Cindy,

                   

                  So I think we can do that...

                   

                  btw in the attached...rather than hard code the years I've used an LoD to pick up the Current Year (and thus previous year)...it just makes it all maintenance free!!

                   

                  So I've mocked up your situation, with bringing Quantity (Volume) in from the secondary source to mimic your case.

                   

                  So I've set up the parameter with 3 options...and the field from this is now

                   

                  [Selected Measure]

                  IF [Select Measure] = 1 THEN RUNNING_SUM(SUM(IIF(YEAR([Order Date]) = {MAX(YEAR([Order Date]))}, [Sales], NULL)))

                  ELSEIF [Select Measure] = 2 THEN RUNNING_SUM(SUM(IIF(YEAR([Order Date]) = {MAX(YEAR([Order Date]))}-1, [Sales], NULL)))

                  ELSE RUNNING_SUM(SUM([Orders (Sample - Superstore)].[Volume TY]))

                  END

                   

                  where for option 3 (from the secondary source) the [Volume TY] is

                  IIF(YEAR([Order Date]) = {MAX(YEAR([Order Date]))}, [Quantity], NULL)

                   

                  I've also created the 3 sheets separately to you can verify its performing as expected.

                   

                  If this seems a little complicated, you could use both mine and Jamieson's suggestions and run the first 2 off one Viz and then sheet swap in the 3rd, build off the secondary data source (as this will exclude you needing to do any blending)

                   

                  Hope that makes sense, and is what you are after...let me know if not (on either score!!)

                  • 6. Re: Add a Quick Table Calculation Filter
                    Yuriy Fal
                    you'd take up an extra 3px

                    With the new container chrome In Tableau 10.2

                    the placeholders between (stacked) objects

                    in a container are 4px on both sides (2 x 4px = 8px)

                    +1px the minimal size of an object (sheet / text / image / blank),

                    so the observed "shift" when swapping sheets is actually 9px.

                    1 of 1 people found this helpful
                    • 7. Re: Add a Quick Table Calculation Filter
                      Simon Runc

                      Hi Yuri,

                       

                      I didn't realise that we have the new funky containers (from TC16) on the 10.2 beta, until I opened up a dashboard today (I'd only done stuff in individual sheets). Looking forward to having a play with them...that whole padding/border thing looked awesome.

                       

                      9px...ooohhh that is a little expensive! (thanks for doing the experiment/math!)...I'll have to rethink how many swaps I do (which is a good thing, as it's always the easy way out when something won't quite work!). We don't use fixed size, so can't use Alexander's nifty trick of floating them over one another.

                       

                      ...right off to play with the new containers

                      • 8. Re: Add a Quick Table Calculation Filter
                        Cindy Easton

                        Simon - I keep trying to respond but get an error.  Trying again (in the event my others happen to show up).

                         

                        I created a test version so you can see what is I'm trying to do.

                        • 9. Re: Add a Quick Table Calculation Filter
                          Simon Runc

                          hi Cindy,

                           

                          Yes that one has come through...

                           

                          So what are the options for the user? Is it that they can choose between seeing 2016 Monthly, 2017 Monthly, 2017 Cumulative for Hires...and within these 3 selections the Goal would show Monthly for 2017 Monthly, Cumulative for 2017 Cumulative and not show to 2016 Monthly?

                           

                          If you can let me know I'm sure we can use a combination of the above techniques to get it working...famous last words!

                          • 10. Re: Add a Quick Table Calculation Filter
                            Cindy Easton

                            I was hoping to eliminate one of the charts and just have one where they could either see the monthly totals or click a button (filter) that would change the entire view to cumulative (all three fields change at the same time goal, actuals and plan).  The chart could be used for two different things 1) to plan on hiring coming in

                            • 11. Re: Add a Quick Table Calculation Filter
                              Simon Runc

                              OK I think I see now (so 2016 is always there as a reference line!)...always helpful to see a workbook, so thanks for posting

                               

                              So I've created (similar to before) the following 3 calculations...driven by our parameter

                               

                              [Actual or Cumulative - Hires 2017]

                              IF [Actual or Cumulative] = 1 THEN SUM([2017 Hires]) ELSE

                              RUNNING_SUM(SUM([2017 Hires]))

                              END

                               

                              [Actual or Cumulative - Hires 2016]

                              IF [Actual or Cumulative] = 1 THEN SUM([2016 Hires]) ELSE

                              RUNNING_SUM(SUM([2016 Hires]))

                              END

                               

                              [Actual or Cumulative - Goal 2017]

                              IF [Actual or Cumulative] = 1 THEN SUM([HiringGoalsxMonth2017].[2017 Goal]) ELSE

                              RUNNING_SUM(SUM([HiringGoalsxMonth2017].[2017 Goal]))

                              END

                               

                              and then replaced all your measures with these ones, so they change from Monthly to Cumulative as the Parameter is changed.

                               

                              As you mentioned that in an ideal work this wouldn't be a drop-down, but a click...I've also created a version that does that!! you can find a full explanation of how this one works here Sheet Swap with Action Filters...the simple version!

                               

                              Sheet Swap.gif

                               

                              Let me know if anything doesn't make sense...or if it's not doing as you want!

                              • 12. Re: Add a Quick Table Calculation Filter
                                Cindy Easton

                                thank you so much for your help!  This is exactly what I was trying to do!!