Weightage Calculation

Mon, 28 Nov 2011 15:28:51 GMT

I am trying to get weightage(OEE Weighted) of a calculation(OEE) in Qlikview. That works but still I get the correct answer only when I select a Plant.

Any idea how to get it without selections. I know that is based on Setanalysis but I am not getting an Idea.

see below screenshots:

without selection:

with selection:

Weightage Calculation

swuehl — Mon, 28 Nov 2011 15:40:32 GMT

Could you post the expressions / dimensions you are using? Or best a small sample app?

Regards,

Stefan

Re: Weightage Calculation

Mon, 28 Nov 2011 15:49:41 GMT

Hi Stefan,

The calculations are a little bit complicated. Please dont get shocked. May be you can help to make them easy.

As per my above diagram the columns are like this.

Dimensions: RefrescoBU, PlantName, MachineName

Availability: = AVG(Aggr(sum({<ProductCategory = {'G'}>} ActivityDuration)/ sum({ <ActivityCategory = {'I'}> / <ProductCategory = {'G'}> /<ActivityCategory = {'F'}> /<ActivityCategory = {'R'}> } ActivityDuration),RefrescoBU, PlantName,MachineName))
Performance: // Here Rangemin is used to cut if the value is more than 1 ->i.e if more than 100 %
=Avg(Aggr(Rangemin(1,(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))/sum({<ProductCategory = {'G'}>}ActivityDuration)),RefrescoBU,PlantName,MachineName))
Quality:
=sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)/(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))
OEE: // Availability * Performance * Quality
=sum({<ProductCategory = {'G'}>} ActivityDuration)/ sum({ <ActivityCategory = {'I'}> / <ProductCategory = {'G'}> /<ActivityCategory = {'F'}> /<ActivityCategory = {'R'}> } ActivityDuration)
*
Avg(Aggr(Rangemin(1,(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))/sum({<ProductCategory = {'G'}>}ActivityDuration)),RefrescoBU,PlantName,MachineName))
*
sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)/(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))
Output: =sum(Output)
OEE * Output: // calculation of OEE * Output
= Sum(Aggr((sum({<ProductCategory = {'G'}>} ActivityDuration)/ sum({ <ActivityCategory = {'I'}> / <ProductCategory = {'G'}> /<ActivityCategory = {'F'}> /<ActivityCategory = {'R'}> } ActivityDuration)
*
Avg(Aggr(Rangemin(1,(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))/sum({<ProductCategory = {'G'}>}ActivityDuration)),RefrescoBU,PlantName,MachineName))
*
sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)/(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))
*
Sum(Output)),RefrescoBU,PlantName,MachineName))
Sum(Total Output) :
Sum(Total Output)
OEE weighted:

= Sum(Aggr((sum({<ProductCategory = {'G'}>} ActivityDuration)/ sum({ <ActivityCategory = {'I'}> / <ProductCategory = {'G'}> /<ActivityCategory = {'F'}> /<ActivityCategory = {'R'}> } ActivityDuration) * Avg(Aggr(Rangemin(1,(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))/sum({<ProductCategory = {'G'}>}ActivityDuration)),RefrescoBU,PlantName,MachineName))* sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)/(sum({<ProductCategory = {'G'}>} StandardValueAddedTime*Output)+ Sum({<ProductCategory = {'R'}>} StandardValueAddedTime*Output)+Sum({<ProductCategory = {'S'}>} StandardValueAddedTime*Output))
* Sum(Output)),RefrescoBU, PlantName, MachineName))/Sum(Total Output)

If you think that the Performance of my QV Application is bad, because of these calculations, it is not the case.

Thanks in Advance

Sravan

Weightage Calculation

swuehl — Mon, 28 Nov 2011 15:58:36 GMT

Your sum(total Output) should always return a value if sum(Output) does. It does not seem to work in your first screen shot. Could you please recheck that the field names are correct and that you don't mixed up field names and expression label names by accident?

In your OEE weighted, I think you're having problems with the implicit DISTINCT qualifier of the aggr() function, you could try to add NODISTINCT qualifier to the aggr() function, but not sure if this will get you correct results.

I would need to go somewhat deeper into the expressions (they are indeed a little bit complex), if you could post some small sample, that would make things much easier.

Regards,

Stefan

Re: Weightage Calculation

Mon, 28 Nov 2011 16:06:18 GMT

Hi stefan,

Thanks for your answer. I will check the points you told me. Here I am now attaching the Application.

Thanks
sravan

Weightage Calculation

swuehl — Mon, 28 Nov 2011 16:36:29 GMT

Sravan

I noticed some Output values are extremely high (like 10^22), some are negative. I this reasonably? (I think that might cause some problems with your total sum).

Re: Weightage Calculation

Mon, 28 Nov 2011 16:44:49 GMT

Stefan,

I also noticed. Do you mean that is the cause for the Weighted value to be wrong?

If I select a BU, It must minimum give the Weightage value for that BU correct.

Regards

Sravan

topic Re: Weightage Calculation in QlikView

Weightage Calculation

Weightage Calculation

Re: Weightage Calculation

Weightage Calculation

Re: Weightage Calculation

Weightage Calculation

Re: Weightage Calculation