5 Replies Latest reply on Jul 19, 2018 11:10 AM by Joe Oppelt

    Subtracting a Calculated Field Issue

    lashon gaines

      Hello!

       

      I am experiencing an issue with Tableau 10.2 in regards to subtracting two calculated fields. I have one data set and two different dates to work with. I have the Last Certified Date and the Scheduled Due Date.  I am looking to subtract the Last Certified Date (completed) from the Scheduled Due date (scheduled)in order to create a variance and convert it into a bar chart. I have 3 calculations.

       

      Completed-Driven by the Last Certified Date

      SUM({FIXED[Group Name],DATEPART('month', [Last Certified]) :COUNTD(IF Year([Last Certified])=2018

      THEN [Plan #]END)})

       

      Scheduled- Driven by the Scheduled Due Date

      SUM({FIXED[Group Name],DATEPART('month', [Scheduled Due Date]) :COUNTD(IF Year([Scheduled Due Date])=2018

      THEN [Plan #]END)})

       

      Variance

      ZN([month completed by grp])-[month scheduled by grp]

       

      When I drop either calculation into the sheet, depending on if I use the Last Certified Date or Scheduled Due Date into my Columns, my totals for the opposite calculation increase for some reason and when I put the Variance into the view it seems to be adding instead of subtracting however, I cannot figure out why it's not subtracting instead.

       

      In my attachment, I have the overall totals that should be used to obtain the proper results titled group-staff which drills down to the totals per group, I just selected one for this example. The second tab, is Enterprise totals, which represent the totals for the entire LOB combined. Each dashboard is driven by either the Last Certified Date or the Scheduled Due Date individually. The tab titled Combo Totals shows the totals when I combine both sets of data together on one sheet in raw number form. The fourth tab is supposed to show the same raw totals in graph form however, when I pulled both options into the view the aggregation seems to add more entries to the totals. I have both date options set to only give me non-null dates and I used ZN in my calculation to ensure there are no null values, however it seems to be pulling in additional values into my totals. I really need assistance with this as I am pretty confident that my calculations are correct. If possible, I need some guru assistance please I have been working on this for a few days now with no resolve and I must report to my manager an update this evening.

        • 1. Re: Subtracting a Calculated Field Issue
          Joe Oppelt

          I'm having a hard time concentrating today, so help me understand what you need.  On the Combo Totals graph, are the values of 4008 for "completed" and 8612 for "scheduled" correct?  If not, are you looking for the filters to impact those numbers?

           

          (The variance of -4604 is mathematically correct given the current values of the other two numbers.)

          • 2. Re: Subtracting a Calculated Field Issue
            lashon gaines

            Hi There,

             

            Thanks in advance for letting me confuse you! I used the combo totals as an example because when I tried to add both calculations to one sheet the numbers increased I guess due to combining the data with different date parameters. The combo chart is correct regarding the look but I expected to see the same totals from the individual sheets. This is my caveat.

             

            I need the sheets in the  tabs labeled Enterprise and Staff to reflect what I created in the sample graph. The numbers in the sample aren’t what they are looking for, they want simple subtraction of the Enterprise tab with the ability to drill down to the LOB and Primary ISC however, because those charts use different date parameters, when I try to join them, depending on which date I use, it increases the number on the opposite calculation on the individual sheets. For example, If you unhide the sheets behind the Enterprise tab, it has one calculation there that is correct however, if you add the other calculation to the view, it skews the totals of that calculation because I am not using the date parameter that’s attached to the calculation and when I add the date field that’s missing, I get the combo totals chart which is not what they want to see. This is throwing off the Variance calculation as well. I have attached a print screen of the excel chart I am looking to duplicate, hopefully that will help clear things up.

             

             

             

            Regards,

             

             

            LaShon A. Gaines

            Operational Metrics Analyst

            Information Security Risk Assessment and Consulting (ISRAC – COE)

            Enterprise Information Security (EIS)

            Office: 980.701.5630  |  Email: lashon.gaines@WellsFargo.com<mailto:lashon.gaines@WellsFargo.com>

            • 3. Re: Subtracting a Calculated Field Issue
              lashon gaines

              Oh and the totals on the combo charts should match the totals on the Enterprise chart and for some reason they aren’t. Once I can get the raw numbers combined on one chart, I can pull the Variance calculation into the field and create the expected bar chart. The 4008 is only correct because I am using the Month of the Scheduled Due Date in the columns. Since that was used first, if you change that to the Last Certified Date, the numbers will not change.

               

               

              Regards,

               

               

              LaShon A. Gaines

              Operational Metrics Analyst

              Information Security Risk Assessment and Consulting (ISRAC – COE)

              Enterprise Information Security (EIS)

              Office: 980.701.5630  |  Email: lashon.gaines@WellsFargo.com<mailto:lashon.gaines@WellsFargo.com>

              • 4. Re: Subtracting a Calculated Field Issue
                lashon gaines

                I’m very close using this view, but as you can see the raw totals are not correct which is throwing off the variance.

                 

                 

                 

                 

                 

                Regards,

                 

                 

                LaShon A. Gaines

                Operational Metrics Analyst

                Information Security Risk Assessment and Consulting (ISRAC – COE)

                Enterprise Information Security (EIS)

                Office: 980.701.5630  |  Email: lashon.gaines@WellsFargo.com<mailto:lashon.gaines@WellsFargo.com>

                • 5. Re: Subtracting a Calculated Field Issue
                  Joe Oppelt

                  OK.  Let's pick one sheet.  Your descriptions have too much internal lingo for me to follow.  Pick a sheet.  (Is SLCERT wrong?)  Tell me what is wrong with it.  What are you seeing and what are you expecting to see?