Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Could you post the expressions / dimensions you are using? Or best a small sample app?
Regards,
Stefan
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
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
Hi stefan,
Thanks for your answer. I will check the points you told me. Here I am now attaching the Application.
Thanks
sravan
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).
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