Reference Date view in analysis


in an Accounting use case we are looking for sales order items, where a goods issue was already booked but no invoice was created yet. That works fine with the process functions and shows always the cases where this situation is currently valid.

My question: Is there a possibility to show also the situation at a reference date? So for example that you can display the amount and value of sales order items that have been in this so called “ship to bill stock” at 04/30/19?

Thank you and best regards,

1 Like

Hi Marco,
The following code should do the trick:
Your reference date is in the TO_TIMESTAMP function.

CASE WHEN PROCESS EQUALS 'Record Goods Issue' AND PROCESS NOT EQUALS 'Create Invoice Document' THEN 'Not invoiced at all' WHEN PROCESS EQUALS 'Record Goods Issue' AND PROCESS EQUALS 'Create Invoice Document' AND PU_FIRST( "VBAP", "_CEL_O2C_ACTIVITIES"."EVENTTIME", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN"='Create Invoice Document') > TO_TIMESTAMP('2019-04-30','YYYY-MM-DD') THEN 'Not invoiced at reference date' ELSE NULL END


Hi Viana,

thanks, I think this will do!