3 Replies Latest reply on Sep 10, 2017 6:46 PM by Okechukwu Ossai

    Filter on Full Data that Utilizes LOD Expressions (Without Context Filters)

    Ryan Spencer

      Hi all,

       

      We are trying to modify a view we have built so that aggregate data will work with a filter. The setup is already quasi-complicated, but I will attempt to outline it below:

       

      1.) We have 4 schools that we are pulling data from (schools A, B, C, and D), but the dashboard is just meant for one of those schools (in this case school B). The rest of the schools' data are used as a point of comparison (this is apparent in the tooltip of the likert chart).

       

      2.) We also have several filters available for this data (in the form of parameters so they apply across multiple dashboards). In our actual database, there are over 10 parameters used for filters.

       

      The goal is to have the "All Schools" values change via the parameters current value. This is possible if we add the three parameters to the context, but our actual workbook contains over 10 dashboards with 10 parameters each (if we add them to context, it exponentially slows down the experience).

       

      Also, we have made this work in the past by duplicating the database, but we are hoping to use Tableau Bridge to automatically update the data in Tableau Online. Bridge currently can't update multiple data sets for the same view, unfortunately.

       

      We are open to any and all suggestions! I have been sifting through all of Tableau's information on LOD expressions, Tableau's Order of Operations, and other information on context filters. Even with this information, I have still not been able to come up with a solution.

       

      We may need to rework our entire workbook, but we are open to anything at this point! Feel free to check out the attached workbook for what I've described.

       

      Thank you for your time in advance. This community rocks.

       

      Best,

      Ryan

        • 1. Re: Filter on Full Data that Utilizes LOD Expressions (Without Context Filters)
          Jennifer VonHagel

          Hi Ryan,

           

          I am not fully following the nitty-gritty of what you're trying to do in the sample data. But I have been in the weeds with LOD calculations, table calculations, and how they mix with filters and context filters, and I think I get the gist of what you want to accomplish.

           

          If you need to include some dimensions in a Table Calc Total or have them be in play on the view for the INCLUDE/EXCLUDE LOD calcs, but don't want to show all the dimensions, you could use your true/false filter calculations to hide rather than filter out dimensions.

           

          So in this example, I moved your para-school filter field onto rows. All values are now in the table.

          I can right click on FALSE, and choose to HIDE this dimension value, rather than EXCLUDE it:

          So now, I haven't filtered out All School's values - they are available to be used in Table Calcs - but only the school whose parameter is selected will show up in the view.

          You can hide the para_school column values... Right-click on True, and un-check Show Header.

           

          Hope this helps, good luck!

          Jennifer

          • 2. Re: Filter on Full Data that Utilizes LOD Expressions (Without Context Filters)
            Ryan Spencer

            Hi Jennifer,

             

            Thanks for the help! I actually figured out a way independently that involved adding each "para_*" dimensions to the All School FIXED calculations, but if I find it doesn't work down the road I will give your answer a try!

             

            Thanks again!

             

            Cheers,

            Ryan

            • 3. Re: Filter on Full Data that Utilizes LOD Expressions (Without Context Filters)
              Okechukwu Ossai

              Hi Ryan,

               

              I was working on this but have just seen that you seem to have figured it out. However, I'd go ahead and post my solution, maybe it will come in handy later. See attached workbook.

               

              My solution doesn't require any filter to be added to context. The idea is to define a new Student Id controlled by the parameters. This new Student Id will be used in all the Count() calculations.

               

               

              1. Create calculated field [Student Id_filtered]

              IF NOT ISNULL([Textual Answer]) THEN

                  IF NOT ISNULL([para_gender]) AND NOT ISNULL([para_age]) AND NOT ISNULL([para_school]) AND NOT ISNULL([para_region]) THEN [Student Id] END

              END

              Add this to the filter shelf and exclude Null.

               

              2. Create calculated field [Overall_Student Id_filtered]

              IF NOT ISNULL([Textual Answer]) THEN

                  IF NOT ISNULL([para_gender]) AND NOT ISNULL([para_age]) AND NOT ISNULL([para_region]) THEN [Student Id] END

              END

              3. Create calculated field [para_age]

              IF [Age Group Parameter] = "All Ages" THEN [Age Group]

              ELSEIF [Age Group Parameter] = [Age Group] THEN [Age Group]

              END

              4. Create calculated field [para_gender]

              IF [Gender Parameter] = "All Genders" THEN [Gender]

              ELSEIF [Gender Parameter] = [Gender] THEN [Gender]

              END

              5. Create calculated field [para_gender]

              IF [Region Parameter] = "All Regions" THEN [Region]

              ELSEIF [Region Parameter] = [Region] THEN [Region]

              END

              6. Create calculated field [para_school]

              IF [School] = [School Parameter] THEN [School] END

              7. Create calculated field [id_count]

              COUNT([Student Id_filtered])

              8. Create calculated field [id_count_row]

              {FIXED [Question Description], [Textual Answer]: COUNT([Student Id_filtered])}

              9. Create calculated field [id_count_total]

              {FIXED [Question Description]: COUNT([Student Id_filtered])}

              10. Create calculated field [% of Total id_count]

              SUM([id_count_row])/SUM([id_count_total])

              11. Create calculated field [All Students]

              {FIXED [Question Description]: COUNT([Overall_Student Id_filtered])}

              12. Create calculated field [All Students per Sat Response]

              {FIXED [Question Description], [Textual Answer]: COUNT([Overall_Student Id_filtered])}

              13. Create calculated field [Overall Satisfaction %]

              SUM([All Students per Sat Response])/SUM([All Students])

              14. Since the view is a 100% bar chart, remember to right click on id_count percent total calculation on the column shelf and select Compute using 'Cell'

              A few formula links were broken in Sheet 2. So take a look to ensure that the chart is working as you'd expect.

               

              Hope this helps.

              Ossai