9 Replies Latest reply on Apr 8, 2013 4:42 PM by P Fro

    Conditionally filtering a field

    P Fro

      I'm not great at writing code so I'm hoping someone out there can help. (See the attached workbook) Here is my question:

       

      I'm looking at a set of markets and I want to compare "# Items" between two different "Campaigns". However, for any market that has a "# Items" as zero, for either or both campaigns, I need to remove that market from the view completely. So, in the sample attached, the final outcome should be a view showing only the totals for Chicago, Detroit and New York for both Fall and Spring. I tried to take a stab at it with a calculated field "New Market List" but my skills are lacking...any ideas?

       

      I know that I can get to the same outcome by just manually excluding the markets with a "0" but I'm looking for an automated method as the dataset I'm working with is much larger and manually excluding Markets would be tedious.

       

      Thanks!

        • 1. Re: Conditionally filtering a field
          Dimitri.B

          See attached workbook for a solution. I modified the data a bit to demonstrate that if the other campaign or both campaigns have zero items the formula works as intended.

          I used table calculation here because you need to check each campaign, otherwise simple [# items] = 0 will only work for markets with both campaigns at zero, i.e. if at least one campaign > 0, then [# items] = 0 is false.

          • 2. Re: Conditionally filtering a field
            P Fro

            Thanks Dimitri, that's exactly what I was looking for!

            • 3. Re: Conditionally filtering a field
              P Fro

              Dimitri,

               

              Quick follow up to this question after reviewing the results a little more: If you take a look at the workbook you created and add column totals, it still totals in the amounts which are filtered out in the data. Do you know why it would do that, and how to change the workbook so that those totals only include the rows which are visible?


              Thanks!

              • 4. Re: Conditionally filtering a field
                Dimitri.B

                That is weird...

                I don't know for sure, but I think it has something to do with the filter item being a table calculation.

                This might be a bug. I'll look into this and may have to contact Tableau Support.

                • 5. Re: Conditionally filtering a field
                  P Fro

                  Dimitri,

                   

                  Just to follow up on this; the "manual" way to solve the issue of totals including the filtered items is to actually drag the market dimension (from your sample workbook) into filters and just hit 'ok'. Then the totals will be accurate. However, any time other filters are changed or anything in the data, changes, you need to "reapply" the dimension filter...it won't automatically continue to filter out the correct items based on the T/F calcualted field that you created. Thanks again for your help on that.

                  • 6. Re: Conditionally filtering a field
                    Dimitri.B

                    Although this works, it doesn't really solve the problem. Dragging Market onto a filter shelf and just clicking OK is the same as manually picking markets that you know to fit the required criteria, i.e. it becomes a complete manual process.

                    The trick here is that when you add Market to filter shelf, Tableau smartly pre-trims the new filter to only select markets that are not filtered by any other filter already in place (e.g. our table calc). When you just click OK, you are applying the second, straightforward and manual, filter on the Market dimension that happens to reflect what the previous filters have already done and it also fixes the totals.

                    This begs the question - why doesn't our table calc filter work for grand totals when it works nicely for Market dimension?

                     

                    I contacted Tableau Support on this and got the following reply:

                    Tableau Support:

                    ...

                    The behavior described in this issue is operating as expected since the Filter being used is built using the LOOKUP(expression, [offset]) Table Calculation Function.
                    The Lookup Table Calculation Function does not remove the data from the view as a typical Filter would, it simply targets a specific row or rows in the view and returns the values of the given expression, as a relative offset from the current row. Since this is not an actual filter then the values that make up the Table remain for the Grand Total to compute the values for.

                    ...

                     

                    This is not easy to grasp, because it involves table calculations. This is how I understand it:

                     

                    Think of it this way: when Tableau processes a table calculation (which our filter is based on), it builds an invisible table somewhere in the background, which aggregates the values correctly using the actual sheet layout and the "compute using" thing, as set by user (it might not do that at all, but let's assume it does).

                    So, in our example, to figure out what markets to show Tableau aggregates the data as specified by "compute using" and only then, based on the aggregated results, determines what markets to leave on the sheet.

                    However, when calculating grand totals, Tableau cannot determine what data rows to include or exclude, because to calculate the filter it needs to arrange the rows into the right formation and aggregate the results, and only then it will be able to see what markets fit the criteria.

                    In other words, grand total is the sum (or avg, etc.) of individual data rows, not the sum of already pre-aggregated cells from imaginary table that we assume Tableau builds to do table calculations.

                     

                    With our table calc filter, Tableau cannot determine True/False for each individual row, it can only do it for aggregation of rows, and grand totals don't work that way.

                    • 7. Re: Conditionally filtering a field
                      Joe Mako

                      The way that I understand it is:

                       

                      Grand Totals and Subtotals are a separate query to the data source, and this separate query happens before Table Calculations and aggregations are performed. So the table calc filter can filter as expected when there is no grand total, but turning on grand total can bring rows back into the display as blank rows. This effect looks similar (but is not in my opinion) to the effect the Pages shelf can have. The rows are all drawn, and then only the marks that meet the filter are drawn.

                      • 8. Re: Conditionally filtering a field
                        P Fro

                        Very interesting; thanks for the follow-up. It doesn't see there is anywhere to go from here since technically it's not a bug, but if you discover any new information that might help I would love to hear it!

                        • 9. Re: Conditionally filtering a field
                          P Fro

                          Coming back around on this issue now that I'm running Tableau 8.

                           

                          I have been using this method for several months now, but it seems that in Tableau 8, the "market" filter isn't working the same way. Originally Dimitri commented that:

                           

                          "Tableau smartly pre-trims the new filter to only select markets that are not filtered by any other filter already in place (e.g. our table calc)."

                           

                          However, in Tableau 8, it seems that the "market" filter isn't taking the other filter into account, even when I add it to context. Tableau still gives me a list of all markets, including those where I found a zero item in the table calc.

                           

                          Any ideas on how I can get Tableau to recognize the original calculation?