7 Replies Latest reply on May 9, 2018 5:09 PM by Okechukwu Ossai

    Table calculation is not filtering other sheets on dashboard

    Ravit Thapar

      Hi Tableau Community,

       

      To illustrate my issue, I have created a dummy workbook (see attached).

       

      Description: A client goes through different phases. If a phase has an 'phase end date' that means the phase is 'complete', otherwise it is 'incomplete'. I have a table calculation that displays 'overall client status' (that is, if any one of the phases for a client is incomplete then overall status is incomplete. In other words, Overall Client Status will be complete only if all individual phases for the client are complete). I achieved this calculation by editing the table calculation to 'restart at every client' (see image attached).

       

      I have 2 worksheets: 1. Computes the overall client status (Sheet1) 2. Computes the Client count (Sheet2)

      Both the above worksheets are on the dashboard.

       

      Issue: I am only able to apply the 'Overall Client Status' Filter to Sheet1 and not on Sheet2.

       

      Expectation: Client count should change based on the 'Overall Client Status' Filter selection. In this dummy workbook's case, Client Count should be 2 (for 'Complete') and 3 (for 'Incomplete')

       

      Any help on this would be appreciated. Thank you!

        • 1. Re: Table calculation is not filtering other sheets on dashboard
          Okechukwu Ossai

          Hi Ravit,

           

          One approach will be to use LOD instead of table calculations.

           

          Step 1:

           

          Step 2:

           

          Hope this helps.

          Ossai

          1 of 1 people found this helpful
          • 2. Re: Table calculation is not filtering other sheets on dashboard
            Ravit Thapar

            Hi Ossai,

             

            This worked just perfect! Thank you so much!

            Also, I'd really appreciate if you could please explain how the LOD calculations are working so that I can have a better understanding in future. (I can understand the intuition but I am not well aware with LOD calculations)

             

            Looking forward to your response.
            Thanks again!

             

             

            -Ravit

            • 3. Re: Table calculation is not filtering other sheets on dashboard
              Okechukwu Ossai

              You're welcome Ravit.

               

              LODs allow you to perform calculations at different data levels. The need for LOD arises because most databases have different levels of granularity and business users often want to see analysis or aggregations performed at those different levels. There are 3 main LOD expressions INCLUDE, EXCLUDE and FIXED.

              FIXED is probably the most commonly used LOD. The LOD you eventually chose to use depends on the problem you are trying to solve.

               

              For example, a retail store may want to see total sales per country which will be {FIXED [Country]: SUM([Sales])} or per region in each country which will be {FIXED [Country], [Region]: SUM([Sales])}, or per city per region in each country which will be {FIXED [Country], [Region], [City]: SUM([Sales])}. Computation at the city level is more granular than the country level. You could also do {SUM([Sales])} or {FIXED : SUM([Sales])}. This will give you total sales for all cities, regions and countries in the database.

               

              Generally, the fields in a view determine the granularity of that visualization. However, LODs gives you the ability to display values at a different level of detail than the one in the view. For example, we were able to display [Overall Client Status] at a less granular level on the same view as [Phase Status] which is at more granular level. Think about FIXED LODs as you would GROUP BY clauses in SQL. So, {FIXED [Country]: SUM([Sales])} is the same as summing up all the retail sales in the database, grouping that sum by country.

               

              So, coming back to your analysis. For [Phase Status], we looked at each End Date of every Phase of each client, and at that level, we checked if the End Date was null. If yes, return 'Incomplete', otherwise return 'Complete'. This level is very granular. Some of your senior managers, are not interested in what happens at this level, they are looking at the bigger picture and will only want to see the overall status for each client. So, we need to perform the calculation at that level also.

               

              So for each client, we checked if ALL individual phase status is 'Complete'. If yes, then return 'Complete', otherwise it is 'Incomplete'. This calculation is at less granular level.

               

              Hopefully, I managed to help you a little. There are many online resources which can help you understand LODs better. However, playing with different LOD's and seeing what the results are seems to be the best way to gain deeper understanding of the concept.

               

              Ossai

              1 of 1 people found this helpful
              • 4. Re: Table calculation is not filtering other sheets on dashboard
                Ravit Thapar

                Thank you so much Ossai for the explanation. I appreciate you putting it down. I'll keep experimenting to better understand the concept. Also, while implementing the above calculations for Phase Status and Overall Client Status, I realized I don't need the LOD calculation for Phase Status and two calculations could be reduced to the below expression:

                 

                • Phase Status => IF ISNULL([Phase End Date]) THEN 'Incomplete' ELSE 'Complete' END
                • Overall Client Status => {FIXED [Client]: MAX(Phase Status)}

                 

                Am I correct in deducing this logic?

                 

                Thanks again.

                • 5. Re: Table calculation is not filtering other sheets on dashboard
                  Okechukwu Ossai

                  The logic looks correct to me. I wasn't sure how your real data is structured, so I went for a very strict solution which will work with most data structures. If you test the new formula on your entire dataset and if it works then it's a very good approach.

                  1 of 1 people found this helpful
                  • 6. Re: Table calculation is not filtering other sheets on dashboard
                    Ravit Thapar

                    Yes, I tested it out on my dataset and it works as per the expectation. Thanks for the affirmation and all the help!

                    • 7. Re: Table calculation is not filtering other sheets on dashboard
                      Okechukwu Ossai

                      You're welcome.

                      1 of 1 people found this helpful