This page contains user submitted questions with development team answers on how to perform calcucations in P2P Analytics Custom web edit. The questions and answers are published here so all the users can learn from the solutions and use the examples in their own analysis.

Questions and Answers

Q: "How to group fields, e.g. Cost Center Code, Account number etc. to higher level items and units?"

Formula:

IF STARTSWITH([Cost center code], "10") THEN "Unit 1"

ELSEIF STARTSWITH([Cost center code], "20") THEN "Unit 2"

...

ELSE "Other unit"

END

Description of formula: The formula first checks all the codes starting with "10", and renames them with wanted naming, here "Unit 1" and continues as long as there is a mapping. Before ending, the formula names all the unnamed as "Rest of the units".

Datasource: InvoiceCodingRowMain

Q: "When suppliers are billing invoices, sometimes the invoice value is greater than the confirmed price in the PO. There are instances that this goes through the matching tolerances for an invoice. With that said, I would need to know how to set the right tolerance levels for certain suppliers so I can setup the right PO matching tolerance levels."

Formula: AVG(IIF(([Matched, net total, company (Order line)]-[Current net total, company (Order line)]>0),(([Matched, net total, company (Order line)])/([Net total, company (Order line)])-1)*100,NULL))

Filters: Order Number (Order) set to exclude Null, Matching Outcome (Order line) set to include only Matched.

Description of formula: The formula first includes only lines which have been overmatched, meaning the matching net total is larger than the current net total. Then it calculates the difference between the two totals, which is further converted into percentage and averaged. The user can use this formula as a filter to only display those lines that have value (not Null), which is a quick way to see each Supplier and the average percentage of overmatched net total in overmatched lines. Bringing the the Line ID to the sheet enables the user go through individual lines.

Datasource: PurchaseOrderLine

 

Q: "I would like to see by supplier purchase orders in which the confirmed delivery date has lapsed and still not having goods receipt."

Formula: IIF([Confirmed delivery date (Order)]<TODAY() AND ISNULL([First goods receipt received time (Order)]),[Order number (Order)],NULL)

Description of formula: The formula first checks that the confirmed delivery date is earlier than the date of today and the goods receipt has not taken place. If these two conditions holds true, the formula returns the Order number, otherwise it gives value Null.

Datasource: PurchaseOrder

 

Q: "When using data source on coding row level, I'd like to use header level measures on my analysis. I need to deduplicate the header level measures results."

Formula: IF FIRST() = 0 THEN SUM([Header level measure, e.g. Net total, company]) END

Description of formula: After placing the calculated field on the canvas, select "Compute using" and change it to the level where the calculation is to be performed. Usually the lowest level, in below example it is the Coding row. The formula checks if the row in the partition is the first one, which effectively deduplicates the measure from occurring multiple times per Invoice ID.

Datasource: InvoiceCodingRowMain/InvoiceCodingRowCustom blended with InvoiceHeader