Creating Calculated Terms

Hi,
I am trying to calculate payments terms in an Accounts Payable Analysis. Right now, my formula is:

SUM((CALC_THROUGHPUT(First_OCCURRENCE[‘Vendor Creates Invoice’] TO Last_OCCURRENCE[‘Pay Invoice’], REMAP_TIMESTAMPS("_CEL_AP_ACTIVITIES".“EVENTTIME”, DAYS)))*(“BSEG”.“WRBTR”))/SUM((“BSEG”.“WRBTR”))

Is it appropriate to do calculations combining BSEG and CEL_AP_ACTIVITIES Table? I would be curious to know if there is a better way to do this.

Hi apenchuk,

Welcome to the Celonis Community :slight_smile:

It’s not clear to me what your trying to achieve with your formula. But in general, you can combine calculations on the BSEG and CEL_AP_ACTIVITIES but you have to be careful about how they are aggregated. BSEG is the case table so it has one row per case. The activity table has one row per activity and therefore many per rows per case. When combining information from different tables its important to be aware which level the calculations are taking place on. If you are operating on activity level then for each activity, the information from the case table located in the row belonging to the case for which the activity is taking place is looked up. This means the same information is repeated multiple times as several activities belong to the same case. If you want an output that calculates on case level it is necessary to aggregate the activities (e.g. by counting them) so you have one value per case that you can then combine with information from the case table without repeating it.

In your case an aggregation on to case level is already included because it calculates the total throughput time of all activities per case. So, no problem to multiple the result with information from the case table.

Generally, if you want to combine information from the activity table with that from the case table its necessary to explicitly aggregate it on case level using pull functions. To understand the syntax of how to use PULL function I recommend reading the help pages to the function in the Celonis documentation (you can find it by opening the IBC > click on the the icon in the top write hand corner with your intial > click on help resources > search for “Pull Up Aggregation Functions”). I’m aware that my explanation of why its necessary to aggregate on different levels was probably pretty confusing so I recommend reading my college Viana’s popular introduction to what Pull functions are, how they work and why to use them: The longest but ultimate guide for being a Pull-Up-Functions expert.

Does this answer your question?

I wish you all the best in your future endeavors with Celonis. If you ever need help with any other questions, feel free to make a new Community posts and we’ll do our best to support you.

Best wishes,

Calandra

Hi Calanda,

I think that was a very helpful description. Thank you for the clarification.

Also, the reference to Viana’s article was a great resource. It will make things much more clear as it goes on.

Best,
Andrew