4 Replies Latest reply on Feb 1, 2013 7:01 AM by Jackie Klein

    Calculation for count of Goals Met

    Jackie Klein

      I have two measure fields that I am trying to determine whether Goals were met for those two fields or not - one is Sales and one is Profit. 

       

      As you can see, on Sheet 2 in the attached, some of the Profit values are blank, therefore, the Goal_Met_Profit is Null.  How do I change that so that it shows a '0' instead of Null?

       

      That way, when I add Goal_Met_Sales and Goal_Met_Profit in the Goal_Met_Total calculation, Nulls won't be present. 

       

      Also, from there, I want to add another tab that will show a bar graph that displays how many states met 1 goal and how many states met 2 goals. 

       

      This is just a sample as my live data has 5 goals, and multiple dimensions that I want to summarize by.

       

      Thanks,

      Jackie

        • 1. Re: Calculation for count of Goals Met
          Robin Kennedy

          Jackie,

           

          Please see the attached workbook for one possible solution.

           

          The key here is to make use of the ZN function, which returns a zero when a NULL is found. Bring in your fields from the secondary data sources into the primary individually, and then use the ZN function on the new primary field.

           

          Hope that helps,

          Robin.

          • 2. Re: Calculation for count of Goals Met
            Jackie Klein

            Thanks.  Ok, does the GOAL_MET_PROFIT from the secondary data source have to be brought over to the primary for the Nulls to be eliminated, or can this be done right in the secondary data source with out brining it over to the primary?

            • 3. Re: Calculation for count of Goals Met
              Dana Withers

              Hi Jackie,

               

              I'm still learning tableau and going through the forums for interesting questions to help me learn about the things I'm no doubt going to run into at a later stage. I'm not sure what it means to bring one field into another but I did have a solution that I think works at least part of the way but then I get an issue with something completely different.

              I've used ATTR to resolve the NULLs. I've named the calculated field Goal_Met_My_Try.

              I needed to repeat some of the calculation in the the totals so I've created a new field for that with a name of My_Try as well.

              Then I can get a graph to show you per state what the total goals met is, but I cannot get it to count distinct states, so it doesn't want to do a graph as you described.

               

              Does that help?

               

              Dana

              • 4. Re: Calculation for count of Goals Met
                Jackie Klein

                Robin - Is there a way on your Sheet 3 tab (Counts by State) to show a solid bar in the graphs instead of the bars showing like they are with lines between each distinct count?

                 

                Thanks