1 Reply Latest reply on May 27, 2011 5:59 AM by Scott Soneson

    Invoice Number - Distinct Count

    Gretchen Harms Expert

      We are wondering how other Ariba customers are finding the number of invoices processed monthly. We are finding that neither Invoice Count nor Invoice Quantity in SpendViz give us a count of unique invoice numbers for a supplier. Pivot tables in Excel also do not give us a count of only distinct invoice numbers.

       

      In one example we see this in SpendViz:

       

      Supplier A, Invoice 1, Invoice Count 0.88888888889, Invoice Quantity 28

      Supplier A, Invoice 1, Invoice Count 0.11111111111, Invoice Quantity 1

       

      In another example we see this is Spend Viz:

       

      Supplier B, Invoice 2, Invoice Count 1, Invoice Quantity 0

      Supplier B, Invoice 2, Invoice Count 1, Invoice Quantity 0

       

      In Excel Pivot Table we get:

       

      Supplier C, Invoice 3, Invoice Count 1

      Supplier C, Invoice 3, Invoice Count 1

      Supplier C, Invoice 3, Invoice Count 1

       

      Obviously in excel it is counting each row for the supplier.

       

      I don't know what SpendViz is doing exactly, I just know it's not giving us the number we are ultimately looking for.

       

      Any help is appreciated.

        • Re: Invoice Number - Distinct Count
          Scott Soneson Novice

          Gretchen,

           

          Our users were running reports with invoice counts and it confused them and me as to why they would get fractional counts. I did some investigation to try to determine how Spend Visibility was calculating the count. I haven't found any documentation that defines how the calculation is done so I tried to back into it based on how the counts react to the data.

           

          The unique combination of Invoice ID and Extra Invoice Key, appears to denotes an invoice. I couldn't determine if Invoice Number played a part because in my sample each Invoice ID was tied to only one Invoice Number.

           

          The reason I was getting fractions was because my filter was splitting some of the invoices. That didn't seem right because my criteria was just accounting date and supplier, and I assumed that an invoice is entered all at one time for one supplier. I found that my fractional count was due to an invoice that had been originally paid in a prior time period and then reversed in the current time period. The invoice had two line items, the original and the reversal. The count showed .5 for the current time period. I also intentionally applied filters to break up invoice's to see what happened to the count and if an invoice had 10 lines and I filtered it so only one of those lines were in the report the invoice count was .10.

           

          In excel the only thing I could suggest is to run a pivot by supplier and invoice number, then copy the results and run a second pivot against the results.

           

          Hopefully this provides some help.

           

          Scott

          1 of 1 people found this helpful