1 Reply Latest reply: May 15, 2014 10:15 AM by Olly Bond RSS

    Detail and summary data on one Export Table

    Andy Hewitt

      Here's my scenario, I have detail data for incoming invoices. The data contains the DATE the INVOICE NUMBER the a LINE NUMBER for each item on the invoice and a DOLLAR VALUE for each line item.

       

      I'm trying to generate an output table that has the DATE, TOTAL NUMBER OF INVOICES, TOTAL NUMBER OF LINES and TOTAL VALUE PER DAY.

       

      RAW DATA

      DATE          INVOICE    LINE ITEM AMOUNT

      3/27/2013    INV0001    L0001     $20.00

      3/27/2013    INV0001    L0002     $15.00

      3/27/2013    INV0001    L0003     $10.00

      3/27/2013    INV0001    L0004     $25.00

      3/27/2013    INV0001    L0005     $30.00

      3/27/2013    INV0002    L0001     $20.00

      3/27/2013    INV0002    L0002     $15.00

      3/27/2013    INV0002    L0003     $10.00

      3/27/2013    INV0002    L0004     $25.00

      3/28/2013    INV0003    L0001     $20.00

      3/28/2013    INV0003    L0002     $15.00

      3/28/2013    INV0003    L0003     $10.00

      3/28/2013    INV0003    L0004     $25.00

      3/28/2013    INV0003    L0005     $30.00

      3/29/2013    INV0004    L0001     $20.00

      3/29/2013    INV0004    L0002     $15.00

      3/29/2013    INV0005    L0001     $10.00

      3/29/2013    INV0006    L0001     $25.00

       

       

      DESIRED OUTPUT

       

      DATE          INVOICE COUNT     LINE ITEM COUNT     TOTAL VALUE

      3/27/2013    2                        9                           170

      3/28/2013    1                        5                           100

      3/29/2013    3                        4                             70

       

       

      I haven't been able to figure out how to do this.

       

       

      Any ideas.

       

      Thanks in advance.

        • Detail and summary data on one Export Table
          Olly Bond

          Hello Andy,

           

          Count() as a measure would work only at the level of aggregation in your summary, so you could filter the data once to give you just one row per invoice (advanced filter on duplicates should work) and make a summary for them by date, then in another summary count up the lines, unfiltered, and sum the dollar amounts. Exporting the two summaries as XLS or MDB, open one in a third model, do an external lookup to the other, and you'll have all your data together.

           

          There may be some clever trickery with CountDistinct - there was a trick of Gareth's I wrote up on my blog a couple of months ago about Unique Data that is sort of tn this area and might help - to let you do it in one or two passes and not three.

           

          Best wishes,

           

          Olly