5 Replies Latest reply: May 15, 2014 9:58 AM by joey RSS

    Sum like squawks

    Stan Truman

      I have a report that I turn into a spreadsheet and on this report we have listed

       

      SQK - Sub_sqk  - Hrs 

      0124----


      1.0

      0124--154--- 2.0

      0124--256--- 5.5

      0124--125--- 3.0

      0250----


      4.0

      0260----


      3.5

      What I want to do is when the sqks are all the same, I want to add the hrs together to create

      one line with the sum of them and also keep what other lines that I'm capturing.

       

      In this case it would be

       

      SQK - Sub_sqk  - Hrs 

      0124----


      11.5

      0250----


        4.0

      0260----


        3.5

       

       

      Thanks

       

      Stan

        • Sum like squawks
          joey

          Stan, I believe simply summary will get exactly what you want.  Just set the key to SQK and the measure to Hrs.  Let me know if you need help creating a summary.

          • Sum like squawks
            Stan Truman

            I used the summary and was able to sum my squawks but when I started adding to the item list the other columns that I wanted in my report, it blew up and started putting the like squawks back in with no summing. It works great as long as you don't add any items to the summary sheet.

             

            Any ideals why it would do that.

             

            Thanks

             

            Stan

            • Sum like squawks
              joey

              When you add item fields, it gives you report level detail.  The item fields prevent the key fields (SQK) from aggregating like fields into a single row.

               

              You can always drill up in the report up to the SQK level to get back to the original high level summary, but you lose the item columns you just added.

               

              Rather than speculate with what you are trying to do, can you give a little more information into what your desired output is?  Do you want to add additional columns that always have the same values for a given SQK?  Or do you want to maintain a certain level of detail lower than the SQK, but add a field of the sum of the records with like SQK values?  Or is there another result you want?

              • Sum like squawks
                Stan Truman

                Joey,

                 

                What I have is a bid sheet that has actual hrs and the bid to do that job (squawks). Sometime ago we added what is call a sub squawk to get a liitle more detail. BUT if any actual hours are charge on the same squawk but a different sub sqk, they don't sum up on the main squawk which has no sub sqk. I guess you could call it the parent squawk. What I need to do is to find all the squawks that have the same squawk number but different sub sqks and sum them up. There are 14 departments that have bids and actuals that I need to sum up to the parent squawk. I need to keep the whole report and just add the like squawks up that have the same squawk number.

                 

                Hope this makes sense.

                 

                Thanks

                 

                Stan

                • Sum like squawks
                  joey

                  I think I understand what you are after, the question is how you would like the final report to look.

                   

                  The first option is subtotal lines:

                  [font="courier"]SQK - Sub_sqk - Hrs   

                  0124----


                  1.0

                  0124--154--- 2.0

                  0124--256--- 5.5

                  0124--125--- 3.0

                  0124 Subtotal:  11.5

                  0250----


                  4.0

                  0250 Subtotal:   4.0

                  0260----


                  3.5

                  0260 Subtotal:   3.5

                  Grand Total:    19.0[/font][/quote]A second option would be to add an extra column:

                  [font="courier"]SQK - Sub_sqk - Hrs   SQK_Total

                  0124----


                  1.0   11.5

                  0124--154--- 2.0   11.5

                  0124--256--- 5.5   11.5

                  0124--125--- 3.0   11.5

                  0250----


                  4.0    4.0

                  0260----


                  3.5    3.5

                    /font[/quote]I'm sure there's other possiblities you might immagine.  Once I know what you would like the final report to look like I can help with the answer.