Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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:

OEEwithoutselection.jpg

with selection:

OEEwithselection.jpg

Tags (1)
6 Replies
MVP
MVP

Weightage Calculation

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

Regards,

Stefan

Not applicable

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.

Thanks in Advance

Sravan

MVP
MVP

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

Not applicable

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

MVP
MVP

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).

Not applicable

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

Community Browser