4 Replies Latest reply on Jun 15, 2018 8:52 AM by Jackson Forrest

    Filtering causes calculation values to disappear

    Jackson Forrest

      I haven't been able to solve this via help sections, so hopefully I can get some help here.  I have a bar chart displaying data over time based on a calculation.  The calculation is meant to average pumpage readings on wells over specified time (monthly) then sum those averages for the month.  To do this I made a separate calculation on each well in my "WellResultCalculations" folder to get the specific values for each well.  I then made a calculation on those called "TotalAverage" that essentially looks like AVG([Well1])+AVG([Well2])+AVG([Well3]) etc... I'm sure there is a cleaner way to do this but I am just getting started with Tableau.  When I put this on the shelf all displays fine, but when I quick filter on a set that I made ("Pumpage_Set") of relevant wells, the data disappears.  The goal is to be able to select wells from the "Well" field in my data, average the readings for those wells and sum those averages for the monthly value displayed on the bar chart.

       

      Any ideas as to why my Pumpage_Set filter clears the data when filtering on anything except all?

       

      Tableau Version 10.5

        • 1. Re: Filtering causes calculation values to disappear
          Mavis Liu

          Hi Jackon,

           

          You have nulls in your field, so please use this instead for TotalAverage which includes the zn function:

           

          AVG(ZN([CM-1]))+AVG(ZN([CM-2]))+

          AVG(ZN([CM-3]))+

          AVG(ZN([CM-4]))+

          AVG(ZN([CM-5]))+AVG(ZN([CM-6]))+AVG(ZN([CM-7]))+AVG(ZN([CM-8]))

          +AVG(ZN([CM-9]))+AVG(ZN([CM-10]))+AVG(ZN([CM-11]))

           

          this will work.

           

          Thanks,

           

          Mavis

          • 2. Re: Filtering causes calculation values to disappear
            Jackson Forrest

            Thanks for your help Mavis, although I am still having an issue with the data.  My filters are now working with your calculation, but strangely the calculation is reporting the average of all wells selected instead of the summed averages.  For instance, AVG(ZN([CM-1]))+AVG(ZN([CM-2])) is effectively reporting the average of all data for these 2 wells instead of taking the average of each well then adding them together like the formula suggests.

            • 3. Re: Filtering causes calculation values to disappear
              Mavis Liu

              Hi Jackon,

               

              Depends how you want the avg to be calculated, with the ZN included it converts all the nulls to 0s so will change the average.

               

              If I do a find the ZN AVG or CM-1 and CM-2 I get:

              0.0003391 and 0.001699 respectively. Adding these up gets me the total of 0.002038 which is correct when using the previous formula I gave you:

               

              2018-06-15_07h40_52.png

               

              Thanks,

               

              Mavis

              • 4. Re: Filtering causes calculation values to disappear
                Jackson Forrest

                Running the ZN function on the individual well calculations within the TotalAverage calculation was actually averaging across all other 10 wells NULL values (value of 0) for each individual well, giving me much lower numbers than I was expecting (as seen in your screenshot).  Interesting that Tableau was doing this although I do not have any null values in my raw data.  I was able to make the averages run on just the desired well data by editing the formulas for each well calculation like this: ZN(AVG(IF [Well]='CM...' THEN [RESULT] END)).  I then just did a simple addition calc for TotalAverage like [CM-1]+[CM-2]+[CM-3]...  This works fine.  I will mark your first response as the correct answer as this was the solution to my initial problem of the filters not working.

                 

                Thanks again!