6 Replies Latest reply on Mar 15, 2013 3:11 AM by Dana Withers

    Is there anything wrong with the subtotal function?

    Kenneth Ho

      Hi,

       

      I built a table with LE used and LE allocated.  Next to the 2 is the variance.  The calculation formula for LE variance is LE used - LE allocated.  The number is correct across each area, but at the subtotal level, then the number is wrong.  What did i do wrong?  Thanks

        • 1. Re: Is there anything wrong with the subtotal function?
          Joshua Milligan

          Kenneth,

           

          It looks like LE Variance is being calculated as a Table Calculation (notice the little delta symbol in the field).  Is this what you intended?  If it is simply a subtraction using two other fields, I would not expect it to show as a table calc.

           

          Several things:

          1. Can you edit the calculated field, copy and paste the code here?

          2. What happens if you right click the LE Variance field in the Measure Values shelf and select "Remove Table Calculation".

           

          I'm guessing #2 might not be possible, which is why I'd be curious to see the code from #1.

           

          3. Would you create a new calculated field named [LE Variance 2]

          with this code:

           

          [LE Used 1] - [LE allocated 1]

           

          And then drag/drop that to the Measure Values shelf right under the existing LE Variance field?

           

          Regards,

          Joshua

          • 2. Re: Is there anything wrong with the subtotal function?
            Kenneth Ho

            1. [LE Variance 1]

            running_sum(sum([LE Used]))-[LE allocated 1]

             

            I inserted [LE Variance 2] with the formula you indicate above ([LE Used 1]-[LE allocated 1]), but it doesn't work because it said something like I can't mix aggregate with non-aggregate.  So i rewrote it, (sum([LE Used 1])-[LE allocated 1]). 

             

            The result with the 2 formula is the same.  The subtotal is still higher.

            Screen shot 2013-03-05 at 9.27.55 AM.png

            I don't know if it has anything to do with the back end data.  but you can see that for the LE used 1, i have to use sum, but LE allocated 1, I will use something like avg or Max to get the distinct value.

            Screen shot 2013-03-05 at 9.30.30 AM.png

            • 3. Re: Is there anything wrong with the subtotal function?
              Kenneth Ho

              Anyone have any ideas?  I have been stuck with this problem for a while.  I talk to the database expert, but he doesn't know how he can help to provide the data the way that Tableau can give me the subtotal value. 

               

              The end goal is to provide difference of the usage and allocation.  so far, the usage number is good, but the allocation with using sum() function will add up all value but if use Max () function, the subtotal will only pick the max value in that area.  I want it to sum all max value.

               

              How should i do it?  Thanks

              • 4. Re: Is there anything wrong with the subtotal function?
                Dana Withers

                I think your sub total is off because you're using a table calc, meaning that it wont take into account that you're also filtering data.

                Is there any way you could post a dummy type dataset to play with? It makes it so much easier to click and try than to theorise

                • 5. Re: Is there anything wrong with the subtotal function?
                  Kenneth Ho

                  Hi,

                   

                  Please find the twbx file.  Please look at LE allocation column, LE variance, hours allocated, and hours variance.  What can i do to report the correct variance subtotal?  Thanks

                  • 6. Re: Is there anything wrong with the subtotal function?
                    Dana Withers

                    Hi Kenneth,

                     

                    Sorry it took a while to get back.

                    I think what you're looking for is not possible from the data format that you have - unless perhaps there is a better table calc wizard out there who knows how to calculate your values at just the right level if that is possible.

                     

                    Basically what you need to do is have a formula that behaves differently for rows than it does for totals. Your data has LE Allocated as the same fixed number for every row of a certain team. To calculate with that you need the distinct value, couldn't find that, but min or max to do the trick. However if you use max on the group of a team, it works well, but if you use max on the group of all teams you simply get the biggest LE Allocated for all teams.

                     

                    I could not find a way to make a calculation that does different things for rows than for totals and I could not find a calculation that somehow sums all distinct values only (I think that would work for both row level and total level in the correct way).

                     

                    Love to know if you find a solution though.

                     

                    Kind regards

                     

                    Dana