8 Replies Latest reply: May 15, 2014 10:02 AM by Olly Bond RSS

    Summary - Summarize unique values only

    rconverse _

      Hello,

       

      I have a file with header and line data that I need to collect.  The header record has the total amount in cash for each order.  The line records list the products and the quantity ordered (which I also need to summarize).  How can I summarize the cash only by the header records and the quantity by the line records?

       

      I've tried a couple of different approaches including the UNIQUE function, but haven't had any success.

       

      Thank you,

      Roger

        • Summary - Summarize unique values only
          Olly Bond

          Hello Roger,

           

          I'd be happy to help. Would it be possible for you to post a fragment of the report?

           

          Best wishes,

           

          Olly

            • Summary - Summarize unique values only
              rconverse _

              Hey Olly,

               

              Below is a small sample.  So my summary adds the quantity and the cash.  The cash is super inflated due to the multiple records.  I thoght I could maybe nest a unique function within the sum function, but that didn't seem to work.  Any suggestions?

               

              Thanks,

              Roger

               

               

              Whse Vendor    Name                           PO # Suf Ty Stg      PO Amt Entered  Due Dt   Exp Dt   Appt Dt  Confirm Send  Ship

              -


              -


              -


              -


              --- -- --- -


              -


              -


              -


              -


              -


              -


              -


              ATLA  71070 DART CONTAINER CORPORATION      432811 00* do  3    11,958.30 09/29/08 10/02/08 10/02/08          yes     EDI   LBI

                                    Product                           PO # Suf    Quantity  Cls

                                    -


                     -


              ---    -


                ---

                                    DCC 12J12                       432811  00          40    1

                                    DCC 24J16                       432811  00          50    1

                                    DCC 6SJ12                       432811  00          50    1

                                    DCC 12B32                       432811  00          20    1

                                    DCC 16MJ32                      432811  00          15    1

                                    DCC 24J24                       432811  00         150    1

                                    DCC 6JL                         432811  00         100    1

                                    DCC 20RL                        432811  00          50    1

                                    DCC 20J16                       432811  00         178    7

               

              ATLA  71070 DART CONTAINER CORPORATION      432873 00* do  3    11,544.43 10/01/08 10/06/08 10/06/08          yes     EDI   LBI

                                    Product                           PO # Suf    Quantity  Cls

                                    -


                     -


              ---    -


                ---

                                    DCC 8SJ20                       432873  00          75    1

                • Summary - Summarize unique values only
                  Olly Bond

                  Hello Roger,

                   

                  I would try defining a summary:

                   

                  Key 1 - Order

                  Measure 1 - Sum(Qty)

                  Measure 2 - Unique(Amount)

                   

                  I got:

                   

                  432811     653     11958,30

                  432873     75     11544,43

                   

                  from your sample data that way. Does that look right?

                   

                  Best wishes,

                   

                  Olly

                    • Summary - Summarize unique values only
                      rconverse _

                      Hey Olly,

                       

                      I think your example works, because you are using the Purchase Order # as your key.  I am using the warehouse (which is the first 4 characters in the headrer records (ATLA)).  When I use that the unique function doesn't work the way I wish it would work.  Or I just flat out don't understand the funtion.

                       

                      Thanks for the effort!

                      Roger

                       

                      10/13/08 Mon 05:12  Co: 1    Lagasse Inc.                            Lagasse Inc                       LUPRO  Oper: rcon Page: 1

                                                                      Lagasse Open Purchase Order Report

                       

                      Funct: LUPRO Start Dt/Time: 05/04/08  4:31 AM Store As: OpenDORC Group:

                       

                      Ranges:                     Begin                    End

                      1 Whse                     ATLA                     TAMP

                      2 Vendor #

                       

                      Options:

                      1 Print Line Items?                        yes

                      2 Sort in (W)hse or (V)endor order?        W

                      3 Print PO Notes?                          no

                      4 PO Type: (P)O, (D)O, or (B)oth?          D

                      5 View (C)onfirmed, (U)nconfirmed, (B)oth? B

                      6 Export to Excel?                         no

                      7   Filename to export to:                 OPENDO.tab

                       

                      10/13/08 Mon 05:12  Co: 1    Lagasse Inc.                            Lagasse Inc                       LUPRO  Oper: rcon Page: 2

                                                                      Lagasse Open Purchase Order Report

                       

                      Whse Vendor    Name                           PO # Suf Ty Stg      PO Amt Entered  Due Dt   Exp Dt   Appt Dt  Confirm Send  Ship

                      -


                      -


                      -


                      -


                      --- -- --- -


                      -


                      -


                      -


                      -


                      -


                      -


                      -


                      ATLA  71019 CHICOPEE                        433045 00  do  2       975.50 10/10/08 10/24/08 10/24/08          no      eBuy  ltl

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            chi 8784                        433045  00          25    5

                       

                      ATLA  71070 DART CONTAINER CORPORATION      432873 00* do  3    11,544.43 10/01/08 10/06/08 10/06/08          yes     EDI   LBI

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            DCC 8SJ20                       432873  00          75    1

                                            DCC 12SJ20                      432873  00          75    1

                                            DCC 32TJ32                      432873  00         200    1

                                            DCC 16J16                       432873  00          84    1

                       

                      ATLA  71070 DART CONTAINER CORPORATION      432945 00* do  3    10,998.81 10/03/08 10/08/08 10/08/08          yes     EDI   LBI

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            DCC 4J6                         432945  00          50    1

                                            DCC 8SJ12                       432945  00          21    3

                                            DCC 12J16                       432945  00          20    1

                                            DCC 14J16                       432945  00          20    1

                                            DCC 32TJ32                      432945  00          40    1

                                            DCC 16J16                       432945  00         251    1

                       

                      ATLA  73782 KIMBERLY CLARK                  432371 01* do  2       264.00 09/04/08 09/11/08 09/11/08          yes     EDI   LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            kcc 09996                       432371  01          11   13

                       

                      ATLA  73782 KIMBERLY CLARK                  433020 00  do  2       155.00 10/08/08 10/20/08 10/20/08          no      EDI   LBI

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            kcc 09506                       433020  00           2    1

                                            kcc 09903                       433020  00           6    3

                                            kcc 09995                       433020  00           2    7

                       

                      ATLA  80003 MORCON,INC.                     433054 00  do  1    21,420.00 10/10/08 11/09/08 11/09/08          no      eBuy  LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            MOR D20500                      433054  00       1,008    1

                       

                      ATLA  80003 MORCON,INC.                     433055 00  do  1    21,420.00 10/10/08 11/09/08 11/09/08          no      eBuy  LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            MOR D20500                      433055  00       1,008    1

                       

                      ATLA  80003 MORCON,INC.                     433056 00  do  1    21,420.00 10/10/08 11/09/08 11/09/08          no      eBuy  LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            MOR D20500                      433056  00       1,008    1

                       

                      ATLA  80003 MORCON,INC.                     433057 00  do  1    21,420.00 10/10/08 11/09/08 11/09/08          no      eBuy  LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            MOR D20500                      433057  00       1,008    1

                       

                      10/13/08 Mon 05:12  Co: 1    Lagasse Inc.                            Lagasse Inc                       LUPRO  Oper: rcon Page: 3

                                                                      Lagasse Open Purchase Order Report

                       

                      Whse Vendor    Name                           PO # Suf Ty Stg      PO Amt Entered  Due Dt   Exp Dt   Appt Dt  Confirm Send  Ship

                      -


                      -


                      -


                      -


                      --- -- --- -


                      -


                      -


                      -


                      -


                      -


                      -


                      -


                      ATLA  80003 MORCON,INC.                     433058 00  do  1    21,420.00 10/10/08 11/09/08 11/09/08          no      eBuy  LTL

                                            Product                           PO # Suf    Quantity  Cls

                                            -


                             -


                      ---    -


                        ---

                                            MOR D20500                      433058  00       1,008    1

                      /CODE

                        • Summary - Summarize unique values only
                          joe.lovati _

                          I'm having a similar issue, in the report example above, since vendor is an append value, it will display that value multiple times in the table, however.  What I'd like to do is get a true count of the unique vendor codes.

                            • Summary - Summarize unique values only
                              Grant Perkins

                              I'm having a similar issue, in the report example above, since vendor is an append value, it will display that value multiple times in the table, however. What I'd like to do is get a true count of the unique vendor codes.[/quote]

                               

                              Joe,

                               

                              Do you need to report this as part of a wider report or just as a list on a stand alone basis?

                               

                              If as part of a report, how does that report need to look? Is it, in effect, a combination of two or more separate analyses that you need to group together?

                               

                              Does the result need to be embedded into the contents of your Datawatch product suite or is it for publishing and usage external to those products?

                               

                              Just trying to work out which approach would be the most practical for you.

                               

                               

                              Grant

                                • Summary - Summarize unique values only
                                  joe.lovati _

                                  Well we use the ES product so I need to develop the summary view with that count along with the count of the details too.  I figured out a way to do this with dollar amounts, but not with character values.

                                    • Summary - Summarize unique values only
                                      Olly Bond

                                      Hi Roger,

                                       

                                      (If you PM me or drop me an email at obond@epo.org[/email], I'll happily send you an example model built from your report.)

                                       

                                      You can group by warehouse (ATLA) by making that the first key value, and as long as you tell Monarch to add subtotals not by this field but by Order, you'll be fine. You can hide columns like this:

                                       

                                      Warehouse. ATLA

                                       

                                      Order: 12345

                                       

                                      Amount: $9876.54

                                       

                                      example product1  ....  ... 10

                                      example product2  ....  ... 20

                                       

                                      Subtotal Qty by Order: ... 30

                                       

                                      You can't subtotal Unique Values so my first approach won't help there, but an easy workaround is to use the Average Value of the Amount as a measure. This works as the Amount is the same for each product in an order.

                                       

                                      HTH,

                                       

                                      Olly