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

Weightage Calculation

Sravan Puppala

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

 

  • Weightage Calculation
    swuehl

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

     

    Regards,

    Stefan

    • Re: Weightage Calculation
      Sravan Puppala

      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

        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