4 Replies Latest reply on Aug 30, 2018 11:35 AM by Joe Oppelt

    Simple mathematical calculated field not working

    Kurt Randle

      Hi,

       

      I have a set of users that report their time on a monthly basis. 12 month period. The total workable hours for the 12 months is 2008 hours. I'm trying to create and OT report to see on an annual basis how many OT hours a user had. My calculation looks like this:

       

       

      But I get some crazy numbers.Looking at first row, 2008 - 2022 = -14 Which would mean that for the year the user worked 14 hours of OT. Not 250,986!

       

       

      Thoughts?

        • 1. Re: Simple mathematical calculated field not working
          Joe Oppelt

          Do 2008 - SUM([Actual Hours])

           

          WHat you have is a calc that is working at the row level -- on every row for the user.  Then you're summing that up, and it makes a magnificent value!

          2 of 2 people found this helpful
          • 2. Re: Simple mathematical calculated field not working
            Joe Oppelt

            Doing SUM([Actual Hours]) says to sum up the total hours worked along all rows (which comes to 2022) and then subtract it from 2008.  This is called an aggregate calc.  You'll notice that when you drag that onto your sheet it will go on as AGG([calc name]) instead of SUM([calc name]).

            2 of 2 people found this helpful
            • 3. Re: Simple mathematical calculated field not working
              Kurt Randle

              Ok makes perfect sense and it is working. If I wanted to do an OT report at the month level. What would be the best way to do that in 1 view? Each month has a different amount of workable hours. How to I tell tableau to only take the month total for a user and subtract it from the months total workable hours?

               

              IF Month = July Then 166 - (How do I get only users' July time?)

              1 of 1 people found this helpful
              • 4. Re: Simple mathematical calculated field not working
                Joe Oppelt

                If you had your sheet partitioned by months, (using whatever date field you use to know what month the user's time card is in), then instead of 2022 for that first user you would have a month-by-month total for the user. 

                 

                You could have a separate little data source with 12 rows, one for each month, with two columns:  a date field (probably in the format of MM/01/YYYY) and a value for the full-employment number of hours for that month.  It could simply be an excel file.


                You could then blend the two data sources on month/year.  (Make the excel file the secondary source.)  Then ATTR([excelfile].[monthMax]) would be available for your math on a month by month basis.

                 

                In fact, that excel file could feed your hard-coded 2008 value.  The sum of all 12 months would add up to 2008.  (And maybe 2016 on a leap year.)


                The concept could even be expanded to a weekly total if needed.  (But it would have 52 rows instead of 12.)

                1 of 1 people found this helpful