6 Replies Latest reply: Nov 28, 2011 11:53 AM by Sravan Puppala

# Weightage Calculation

Hi

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

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

Regards,

Stefan

• ###### Re: Weightage Calculation

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.

Sravan

• ###### Weightage Calculation

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

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

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

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