9 Replies Latest reply on Jun 8, 2018 10:53 AM by Krishna Dudhela Branched from an earlier discussion.

    Only want to show Grand Total Row

    Krishna Dudhela

      Hi Joe,

       

      I am trying to display just the grand totals.

      The above given solution doesn't seem to work with my case. Please take a look below.

       

       

       

      Could you please take a look at my workbook and tell me where am I going wrong?

       

       

      Thanks,

      Krish.

        • 1. Re: Only want to show Grand Total Row
          Joe Oppelt

          Krishna -- Branching this to a new thread.

          • 2. Re: Only want to show Grand Total Row
            Joe Oppelt

            Grand totals of aggregates are flaky sometimes.

             

            In the attached I made my own grand total this time.

             

            Sheet 2(2) is a copy of your original.  I added my own grand total calc, which is a window_sum of your AGG() calc.  When it's formatted using 00:00:00 like your calc, I get the right number.  But there is in per Task Type (which you need to have on the sheet to get the individual values.)


            Right click on my calc and select "Edit Table Calc".  You will see this:

             

             

            Notice that I selected Task Type instead of relying on TABLE(down).  Right now on this sheet the two are equivalent, but when  move task type off ROWS, this setting becomes important.


            Go to sheet (3).  Here I took [hh:mm:ss] off TEXT, and I dragged [Task type] from ROWS to Details.  Now I get x-many copies of my calc.  (One per Task Type.)

             

            Go to Sheet (4).  Here I made a calc called [index].  I set the table calc setting on this to match my Calculation2 setting.  And then I selected for value = 1.  Now we just see one copy of the calc value.

             

            It's a long route to do what you expected to see when you just took [Task Type] off the sheet.

             

            One thing that was happening with task type off the sheet is that the AVG([Time in sec]) changes from the average for each task type to the average of all the rows.  With that, the calculation for [hh:mm:ss] would be using the wrong starting value.  that's why we needed to do table calcs with Task type added into the mix.

             

            See attached.

            • 3. Re: Only want to show Grand Total Row
              Krishna Dudhela

              Hi Joe,

               

              Thanks for your response. But my grand total deviates from what it should be.

               

               

               

              Any solution for this?

               

               

              Thanks.

              • 4. Re: Only want to show Grand Total Row
                Joe Oppelt

                I'm not sure why the grand total on sheet 2 shows 161:59:34.  In your original workbook is showed 161:18:93.  And the table calc gets that value.

                 

                And even when I look at that number, why doesn't it calc to 161:19:33?

                 

                I'm not that familiar with converting integer values of seconds into HH:MM:SS using the formatting like this.  But I can tell you that the 161:18:93 is what you would get if you add up the individual numbers by hand on sheet 2.

                • 5. Re: Only want to show Grand Total Row
                  Krishna Dudhela

                  I have worked upon the calculation in the latest attached workbook to eliminate discrepancies. i.e, I wanted to display in hh:mm:ss format. So, I have edited accordingly.

                   

                  none of the time hh:ss: must exceed 60

                  • 6. Re: Only want to show Grand Total Row
                    Joe Oppelt

                    The very first sentence in my first reply is probably why the grand total on Sheet 2 doesn't give you the same value as the viz in sheet 3.  Grand Totals of aggregates are flaky sometimes.  If your measure on sheet 2 were just displaying [Time in sec] as a raw number, then the grand total would sum up correctly.  But [hh:mm:ss] is an aggregate calc because you are aggregating (AVG) in it.  Trust the table calc on sheet 3.  Display that and don't rely on the Grand Total from Sheet 2.

                    • 7. Re: Only want to show Grand Total Row
                      Krishna Dudhela

                      Hi Joe,

                       

                      But my requirement is to display the report in this format.

                       

                       

                      The below two labels Have been created in separate worksheets and placed on the dashboard.

                       

                      The label below gives wrong info. Whereas it must display 8days 17hours 5 minutes and 13 seconds.

                       

                      Any workaround?

                       

                       

                      I used the below formula to display the grand total but it doesn't aggregate further.

                       

                      IIF([Avg Time in seconds] % 60 == 60,0,[Avg Time in seconds] % 60)// seconds

                      + IIF(INT([Avg Time in seconds]/60) %60 == 60, 0, INT([Avg Time in seconds]/60) %60) * 100 //minutes

                      + IIF(INT([Avg Time in seconds]/3600) % 24 == 0, 0, INT([Avg Time in seconds]/3600) % 24) * 10000 //hours

                      + INT([Avg Time in seconds]/86400) * 1000000 // days

                       

                      It displays the result as you see in image1.

                      • 8. Re: Only want to show Grand Total Row
                        Joe Oppelt

                        Like I said before, I'm not that familiar with formatting an integer value of seconds into days and hours, etc.  If I were doing that I would probably break it apart into separate tokens (days, hours, minutes, etc.) and concatenate them into a string.  (And I know that's not the most efficient way, but it's still how I would approach it.)

                         

                        What I've been answering in this thread is helping you get a reliable total value, which is to do it with the table calc I provided.

                         

                        I'm going to have to leave the formatting of that value up to you.

                         

                        But as for displaying the Sheet 3 total, put it on the dashboard as a separate sheet and size/format it to look like it's part of the overall data sheet.

                        • 9. Re: Only want to show Grand Total Row
                          Krishna Dudhela

                          Hey Joe,

                           

                          My problem has been solved.

                           

                          In the calculation, if you use TOTAL(aggregated calc)  instead of window_sum(aggregated calc), it works fine.

                           

                           

                          Thanks though.

                           

                           

                          Regards,

                          Krish.