topic Re: aggr() function for OEE calculation? in QlikView

aggr() function for OEE calculation?

lukasj — Sat, 16 Nov 2024 19:59:59 GMT

Hello QlikvView community!

I have a problem where I am trying to calculate an OEE value. This is done by multiplying A*P*Q. It all works fine when the "P" has only one Ideal Run Rate but when there are several different, and they have been running for different amount of time the aggreagated "P" needs to take that into account. This is displayed in the attached .qvw table.

I need help with the code that needs to be put into the "vAvailiability" Variable so that "vOEE" can be calculated correctly.

A = Availability

P = Performance

Q = Quality

Thank you for any assistance!

Re: aggr() function for OEE calculation?

martinpohl — Fri, 27 Sep 2019 11:00:22 GMT

see attached file.

You have to set all variables without =, otherwise the calculated value is in the variable.

Then you have to use the variable with $(=v

Regards)

Re: aggr() function for OEE calculation?

lukasj — Fri, 27 Sep 2019 11:16:02 GMT

Thank you for a fast response! But this does unfortunately not solve my issue.

The missing Performance "vP" number and the "vOEE" is now an average of the IdealRunRate divided by ProducedItems. It does not take into account what IdealRunRate each article has, nor how many ProducedItems it has.

For example 50% Performance on both Article A+B should not be able to result in a higher Performance.

Regards, LJ

Re: aggr() function for OEE calculation?

lukasj — Tue, 01 Oct 2019 09:49:57 GMT

So I finally solved it. Had to take account for Date and change some of the calculation steps. Below is the result to calculate Performance when items have different IdealRunRate. If there are other Fields such as Machine etc these have to be added to the aggr function as well.

=sum(aggr ( (sum(ProducedItems)/ (sum(IdealRunRate)*sum(RunTime)) )*sum(RunTime) ,Article & ,Date)) /sum(RunTime)