6 Replies Latest reply on Jun 8, 2018 10:34 AM by Simon Runc

    Sum up aggregate measure with none aggregate measures

    kevin smith

      I have a worksheet linked to a data set that shows  ‘unique id’, ‘person name’, ‘movement type’,’job function’, ‘date’

       

      problem:

       

      i created an aggregate calc. Field that has if conditions depend on ‘job function ‘ name this ‘graduate’ which depend on ‘job function‘ it Can be negative or postive sum.

       

      I want to be able to sum up all my movement types by job function but I can’t make it work with ‘graduate’ movement being either positive or negative . Is there a work around?

       

      Thanks,

      KW

        • 1. Re: Sum up aggregate measure with none aggregate measures
          Simon Runc

          hi Kevin,

           

          Hard to be exact without seeing an example workbook, but here are 2 ways which might work for you (assuming I've understood you issue)

           

          One is to create a Positive/Negative dimension and use that in the detail of the viz (I've used the colour shelf)

           

          The other is to create different measures for positive and negative values and use the Measure Names/Values to create the Viz.

           

          Hope that helps, but let me know if I've missed the requirement!

          • 2. Re: Sum up aggregate measure with none aggregate measures
            Jim Dehner

            Good morning Kevin - without seeing your twbx workbook I'm not certain I understand your issue but I think you are saying you created and aggregate and now want to use it in another calculation with fields that are not (or can not ) be aggregated -

            there are several options - first when you create the aggregate "Graduate" you can create with with an LOD - the LOD expression does an aggregation but the result is not an aggregate in itself and therefore can be used in other calculations

            second you could also use table calculations see window_function or running_function on your Graduate field

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Sum up aggregate measure with none aggregate measures
              kevin smith

              Thanks for the all the suggestions, below is a example of what my problem is.

               

              Field in my data set are pretty simple :

               

              ‘unique id’, ‘person name’, ‘movement’, ’job type’, ‘date’

               

              Movements are all measures simple sum function.

              'Graduate' is a function with 2 conditions depend on Job type.

               

              (if [Group Description] = "Graduate" and [Associate Type (group)]="Leader" then  [Number of Records]

              elseif [Group Description] = "Graduate" and [Associate Type (group)]="Trainer" then ([Number of Records]*-1) end)

               

              How can I sum up total movements including 'Graduate'?, I think there's something flawed in my logic just couldn't think of anything else. Thanks for looking.

               

              Job TypeMovementJanuaryFebruaryMarchAprilMay
              Leader
              Gross In1.01.01.01.01.0
              Gross Out-5.0-5.0-5.0-5.0-5.0
              Graduate75.083.067.075.064.0
              Totalsum()sum()sum()sum()sum()
              Trainer
              Gross In20.020.020.020.020.0
              Gross Out-15.0-15.0-15.0-15.0-15.0
              Graduate-75.0-83.0-67.0-75.0-64.0
              Totalsum()sum()sum()sum()sum()
              • 4. Re: Sum up aggregate measure with none aggregate measures
                Simon Runc

                hi Kevin,

                 

                Not sure I fully understand...is there anyway you work up an example workbook (here's a quick video on how to create an anonymized version of your issue Anonymize your Tableau Package Data for Sharing )

                 

                So do you want the "graduate" measure to be the sum of Gross In and Gross Out...so for Trainer in January it would be 5? Do you then want this 5 to be carried forward into February, or does that month start again (i.e. should that value be 5 again, or 10?)

                 

                I also don't know where  [Group Description]  or [Associate Type (group)] come from? as you said your data on has those 5 fields.

                • 5. Re: Sum up aggregate measure with none aggregate measures
                  kevin smith

                  sorry bout that, here's an update. I'll try to load a sample later once I can watch the video(block on my network).

                   

                  'Gross in' - sum(number of records) = 'gross in'

                  'Gross out' - sum(number of records)*-1 = 'gross out'

                   

                  'Graduate'  = see below

                   

                  (if [movement] = "Graduate" and [Job Type (group)]="Leader" then  [Number of Records]

                  elseif [movement] = "Graduate" and [Job Type (group)]="Trainer" then ([Number of Records]*-1) end)

                   

                  goal is to sum up all 3 to get to a total

                   

                  gross in + gross out + graduate

                  • 6. Re: Sum up aggregate measure with none aggregate measures
                    Simon Runc

                    Cool. Yes a workbook would help.

                     

                    As a guess...I think it's the

                     

                    'Gross in' - sum(number of records) = 'gross in'

                    'Gross out' - sum(number of records)*-1 = 'gross out'

                     

                    If you look in the field (that tableau generates) called "number of records" it's just the number 1

                     

                    So your gross out is doing the *-1 after the SUM...so if I have 10 rows, where 5 of them I want counted at +1 and the other -5 as-1 (lets add a dimension for each set as 'plus', 'minus'. If I do the sum of number of rows I'll get 10, and then negative it I get -10. However if I do the negative first and then aggregate I'll get -5 and +5.

                     

                    So Gross In should be SUM(1) and Gross Out SUM(-1)

                     

                    Just a thought on the order in which you negative and then aggregate.