Hallo everyone,Iam trying to get the Sum of Order Quantity(Planned) , but there is multiple occurrence of Operation , its getting multiplied by the Occurrence, Can anyone help me , how to get the Distinct of the Value

Hey there,

seems there happens more under engine hood. Possibly you are using different tables? E.g. Head and Position Table?

What do you want to do exactly? What tables and relations are you using?

Those possible varianty for solution come to my mind:

COUNT(PU_FIRST(Head_Table, Position_Table_Field))

or using

COUNT_DISTINCT(...)

or using

COUNT(DISTINCT ...)

depends on what you want to do :-)

Hey Sarveshwaran,

Should the value be 18 for all occurences?

An option if the value is always the same for all occurences within an ARBPL: would be to use PU_FIRST(case = 73165CP, dimension) or if it's always the same AVG(). The real question is what would cause issues in your formula. Are there dimensions for which the count is not equal for each instance?

Hallo @1460069522 ,

Thanks for the feedback, in that case its Ok, but i need to calcluate the Sum of Quantity, for example, i have 100 Production order in one Worksation, each oder may have different Order Quantity. but at end I want to get the Sum of the Order Quantity for 100 Orders in that workstation,

but the main Problem is the some Order are Partially Deliverd in the workstation,

Ex. Order Number 50 the order Quantity is 10 but the delivery is two times each 5 quantitiy. but when i make the Sum then iam getting it as 20 because it confirmed for 2 times . thats the main problem , i want to get the sum of the Order for Distinct Workstation and Distinct Order number.

For more clarfication see the attachement

Thanks in advance .

Regards,

Sarveshwaran

Hey Sarveshwaran,

That's a harder problem to solve indeed. You're actualliy wanting to aggregate based on dimensions before performing another aggregation. Celonis does not handle such queries well. An option is to create multi-event logs where the case remains the same but the second event log is based on the ARBPL.

Another option would be to review the docs for DOMAIN_TABLE() and CONSTANT() equations, though I'm not sure they will work in this situation.

Would be interested in your solution if you figure it out.

Maurits

I think I found a solution, using the index_order with partition by functionality. That way you can select the first row of each ARBPL with the following code:

   INDEX_ORDER ( "GAMNG",   ORDER BY (""EVENTTIME" ASC),  PARTITION BY ( "ARBPL" ))

Using that as a condition in your PU function results in only getting the first row of each ARBPL and than taking the sum over all ARBPL's. So the formula looks like:

SUM(

PU_FIRST ( DOMAIN_TABLE("ARBPL") , "GAMNG" ,   

   INDEX_ORDER ( "GAMNG",   ORDER BY (""EVENTTIME" ASC),  PARTITION BY ( "ARBPL" ))=1 ))

Hope this works

Hallo @Maurits Akkerman 

Thank you so much for the Clarification.

But still i have one small doubt, is there any possibility to bind 2 table without 1:N relation ,

I have getting one error , already i have made some bind in the formula that you gave, but at the end i got stuck up with this error.

 it would be very helpful if you have any suggestion to resolve this issue.

Thanks in advance for your support.

Regards,

Sarveshwaran Murugan

Hallo Sarveshwaran Murugan,

Your error msg sound to me like there is direct 1:n relation. You have to use PU_xxx funktion instead of bind, if you want to agregate 1:n relation. Bind is only for 1:1 relation.

KR

Alex

Hey Sarveshwaran,

As Alexander mentioned, bind does not work for 1:N relationships. What exactly is the reason you are using the BIND formula here? Could you use a JOIN or PU function instead?