4 Replies Latest reply on Nov 7, 2016 7:53 AM by Dilyana Suleymanova

    Add a formula filter to dashboard action filter?

    Adrian Lee
    Adrian Lee Aug 22, 2016 2:36 AM

      I've got a workbook I'm working on using public data Tableau Public

      The basics are working as I want, you see the map, you click on one of the coloured areas and a time series chart appears below the map showing the change over time.

       

      I want to add a couple of comparisons to this data , for example I want the time series chart to show a line for the whole coloured area so people can see how one part of it compares to the overall average.

      I have the figures as separate rows in the data but of course when the action filter is triggered they get filtered out along with everything else.

       

      If I try editing the action filter for sheet 2, it has the condition tab and I was hoping I could add [ward code] = "E10000027" to the formula box so it would also keep the relevant rows of data, but that stops the chart working altogether.

       

      I could achieve it by getting people to choose an area from a parameter drop down but I'd rather let them click on the map so they don't need to know the name of the area they are interested in.

      • 575 Views
      • Categories: Actions & Filters
      • Tags:
        • 1. Re: Add a formula filter to dashboard action filter?
          Simon Runc
          Simon Runc Ambassador Aug 31, 2016 9:16 AM (in response to Adrian Lee)

          hi Adrian,

           

          So one way of doing this is to use a FIXED LoD. FIXED LoDs are calculated before a (regular dimension) filter is applied...

           

          In the attached I created

          [Claimant Rate Average]

          {FIXED [Date]: AVG([JSA Claimant Rate])}

           

          so this returns to every row the Average for each Month...which means when we filter on a Ward, it still has access to (and displays) the overall average for each month.

           

          So the issue is, that your Average (in the E10000027 ward) isn't the same as the average as calculated from all the wards averages. I assume this is either to do with missing data, or that the data is weighted by population. If you still have (and can add to your data) the populations for each ward, we can amend this calculation (I think!...famous last words!) so take account of the weighting...but instead of that, we can do something a little more creative!

           

          [Claimant Rate from E10000027]

          {FIXED [Date]: AVG(IIF([Ward Code] = "E10000027", [SomersetRate],NULL))}

           

          So this takes the Claimant Rate from E10000027, and applies it to all rows (NULLS get ignored in AVG...well all calculations!)...so again when the filtering is applied it still shows. I've put both versions on your chart (and sheet 3 shows you what's going on).

           

          Hope this helps, and makes sense...please post back if not.

          • 2. Re: Add a formula filter to dashboard action filter?
            Adrian Lee
            Adrian Lee Aug 31, 2016 9:42 AM (in response to Simon Runc)

            The data used is downloaded directly from NOMIS so I don't have access to the raw data, hence the Somerset figure being an additional row rather than an aggregation.

            Some data is suppressed due to low numbers and they do rounding of the figures before calculating the rate, so later aggregation of the data isn't going to work.

             

            This is becoming a common problem with Tableau.  A lot of what I do involves showing a data point with a comparison of a larger area (district, county or national often) for which I may not have full data.  For example comparing local school results to national figures, we have all the raw data for schools in our area, but only have the summary figures for the rest of the country or as with this example, I only have summary stats provided full stop.

             

            I've given up on this example for now but I know there are going to be higher priorities cases where people will want me to be displaying comparison data for it to be useful.

            • 3. Re: Add a formula filter to dashboard action filter?
              Simon Runc
              Simon Runc Ambassador Aug 31, 2016 9:52 AM (in response to Adrian Lee)

              hi Adrian,

               

              Yes I can see the use-case...so hopefully the second option I provided does the trick?

               

              ...However, it looks like you've had to 'hack' together this data together in a (less than ideal) union-type thing, so that Somerset total figure appears as a ward (when it's not...and then causes problems of having to filter it out when you only want ward stuff...etc.). Have you thought about data blending? This way you have one data-source at Ward level, and a second at county and then just blend on month (and maybe County if you have more than one county to look at)?

              • 4. Re: Add a formula filter to dashboard action filter?
                Dilyana Suleymanova
                Dilyana Suleymanova Nov 7, 2016 7:53 AM (in response to Adrian Lee)

                Hi Adrian,

                I would like also let the chart appears, when I click to another chart. What were your filter recruitments?

                 

                Thank you in advance!

                 

                Kind regards

                Dilyana