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
Page 1 / 1
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
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
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:
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
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
Hi Oli,
in order to compare these values, you could use following formulas.
Match of PR to PO value
Match PO to Invoice Value
For Match PR to Invoice Value you could do something like:
Best regards,
Viana
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
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
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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.