Skip to main content
Question

PR vs. PO vs. Invoice value

  • November 27, 2019
  • 4 replies
  • 9 views
.
Level 5
Hi all, Id like to compare the Purchase Requisition (PR) value to the PO value and to the Invoice value. Therefore, Ive added an OLAP table with the PO document numbers, PO Items and the PR number as dimensions and the PO Net Price (EKPO.NETWR) and the PR valuation price (EBAN.RLWRT) as KPIs. Afterwards, Ive calculated the difference in percentage between PO and PR value which works fine. Now, Ive tried to add the Invoice value as a third KPI to this analysis in order to see all differences within one view. Therefore, Ive added the Invoice Value (EKBE.REEWR) to the KPIs, however received following error message: No common parent between tables could be found please check your schema. The tables EBAN and EKBE do not have a common parent in the schema I do understand the issue, however do not know if and how I could solve it, in order to have all three values (PO, PR, Invoice) in the same table. Any ideas? Thanks, Oli

4 replies

V
Level 0
Hi Oli, we typically compare these values by aggregating them with a PU_SUM to check if they match on the item level. In this way you can compare whether the values match or not. Would that be an option for you? Best regards, Viana
.
Level 5
  • .14600836
  • Author
  • Level 5
  • November 29, 2019
Thanks Viana for the feedback! Could you please give an example how such a formula would look like for this use case. In the end Im looking for three KPIs here:
  • Difference PR to PO value
  • Difference PO to Invoice value
  • Difference PR to Invoice value
The idea was basically to show the PR, PO and Invoice value next to each other and on the level of PO item level (which was not possible see error message in my initial post). A second step would be to compare the values with each other and show some KPIs on those differences. Thanks, Oli
V
Level 0
Hi Oli, in order to compare these values, you could use following formulas. Match of PR to PO value CASE WHEN (((("EKPO"."NETWR"/EKPO.MENGE) - (PU_SUM("EKPO","EBAN"."RLWRT")/PU_SUM("EKPO","EBAN"."MENGE"))) >= (-1*("EKPO"."NETWR"/EKPO.MENGE))) AND ((("EKPO"."NETWR"/EKPO.MENGE) - (PU_SUM("EKPO","EBAN"."RLWRT")/PU_SUM("EKPO","EBAN"."MENGE"))) <= ("EKPO"."NETWR"/EKPO.MENGE))) THEN 'Match of PR to PO value' ELSE 'No Match' END Match PO to Invoice Value CASE WHEN (((("EKPO"."NETWR"/EKPO.MENGE) - (PU_SUM("EKPO","EKBE"."REEWR")/PU_SUM("EKPO","EKBE"."MENGE"))) >= (-1*("EKPO"."NETWR"/EKPO.MENGE))) AND ((("EKPO"."NETWR"/EKPO.MENGE) - (PU_SUM("EKPO","EKBE"."REEWR")/PU_SUM("EKPO","EKBE"."MENGE"))) <= ("EKPO"."NETWR"/EKPO.MENGE))) THEN 'Match PO to Invoice Value' ELSE 'No Match' END For Match PR to Invoice Value you could do something like: CASE WHEN (((("EBAN"."RLWRT"/"EBAN"."MENGE") - (PU_SUM("EKPO","EKBE"."REEWR")/PU_SUM("EKPO","EKBE"."MENGE"))) >= (-1*("EBAN"."RLWRT"/"EBAN"."MENGE"))) AND ((("EBAN"."RLWRT"/"EBAN"."MENGE") - (PU_SUM("EKPO","EKBE"."REEWR")/PU_SUM("EKPO","EKBE"."MENGE"))) <= ("EBAN"."RLWRT"/"EBAN"."MENGE"))) THEN 'Match' ELSE 'No Match' END Best regards, Viana
.
Level 5
  • .14600836
  • Author
  • Level 5
  • December 13, 2019
Thanks for the solution!! Ive changed the formulas slightly in order to not just show if values match but also what is the differene in percentage between the values
Community Code of ConductAccessibility statement