
Re: Aggr function within a sum function
Apoorva Dhulehole Dec 4, 2017 11:18 PM (in response to Apoorva Dhulehole)Anybody had a chance to look into this?
Thank you.

Re: Aggr function within a sum function
Sunny Talwar Dec 5, 2017 7:39 AM (in response to Apoorva Dhulehole)Please share a sample to check this out

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 5, 2017 11:50 PM (in response to Sunny Talwar )Hi Sunny,
PFA!
Week 1 value = 86.02%
Week 2 value = 86.25%
When both the weeks are selected, graph should display values for week 1, week 2 and also YTD which is = 86.14%
Thank you.

OEE.qvw 412.8 K

Re: Aggr function within a sum function
Sunny Talwar Dec 6, 2017 4:38 AM (in response to Apoorva Dhulehole)Try this
=Avg(Aggr((sum(aggr(if(F1='ONLine',sum({$<F1={"ONLine"},Station={"BMM"}>}"Operating Time [h]")/sum({$<F1={"ONLine"},Station={"BMM"}>}"Planned Prod. Time [h]"),
if(F1='OFFLine',sum({$<F1={"OFFLine"}>}"Operating Time [h]")/sum({$<F1={"OFFLine"}>}"Planned Prod. Time [h]")
*sum({$<F1={"OFFLine"}>}"Planned Prod. Time [h]"))/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>} "Planned Prod. Time [h]")
)*sum({$<Station={"BMM"}>}"Planned Prod. Time [h]")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL <WeekNumber> "Planned Prod. Time [h]"),F1,Line, WeekNumber))), WeekNumber))

OEE.qvw 421.2 K

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 6, 2017 4:57 AM (in response to Sunny Talwar )Hi Sunny,
Perfect! You are a GENIUS
Thanks a lot!

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 8, 2017 5:07 AM (in response to Sunny Talwar )Hi Sunny,
There is a small issue here in the graph where I tried your expression. For YTD, the value shall be 85.66% and not 85.57%. I think the "Avg" function here is causing this issue. Any idea how I can get this number right?
In the attached qvw you will find the graph I am mentioning about. Hope you can help me here!
Thank you.

OEE.qvw 415.5 K

Re: Aggr function within a sum function
Sunny Talwar Dec 8, 2017 7:08 AM (in response to Apoorva Dhulehole)How are you coming up with 85.66%? The way this chart is calculating average is doing this
83.70% + 87.45% = 171.15%/2 = 85.57%
What is the logic behind seeing 85.66%?

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 8, 2017 7:22 AM (in response to Sunny Talwar )Hi Sunny,
It should not be average. When we select two weeks, the formula is the same but it has to calculate for two weeks.
I am attaching the file which has this graph also here.
Only problem I am facing with this graph is, when I select two weeks, it just sums up and shows one bar. As explained in the original post I would like to see values for two weeks and also the YTD. By applying the formula that you provided me, worked for individual weeks, but for more than one week, it isn't working as it is taking the average of both the weeks. Without doing average I get 85.66% as YTD which is the expected value. How do I do this?
Hope I am clear.
Thank you.

OEE.qvw 419.2 K

Re: Aggr function within a sum function
Sunny Talwar Dec 8, 2017 7:28 AM (in response to Apoorva Dhulehole)Try this expression
If(Dimensionality() = 0,
(sum(aggr(if(F1='ONLine',sum({$<F1={"ONLine"},Station={"BMM"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"ONLine"},Station={"BMM"}>}"Operating Time [h]"),
if(F1='OFFLine',sum({$<F1={"OFFLine"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"OFFLine"}>}"Operating Time [h]")
*sum({$<F1={"OFFLine"}>}"Operating Time [h]")/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>}"Operating Time [h]")
))*sum({$<Station={"BMM"}>}"Operating Time [h]")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL "Operating Time [h]"),F1,Line))),
(sum(aggr(if(F1='ONLine',sum({$<F1={"ONLine"},Station={"BMM"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"ONLine"},Station={"BMM"}>}"Operating Time [h]"),
if(F1='OFFLine',sum({$<F1={"OFFLine"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"OFFLine"}>}"Operating Time [h]")
*sum({$<F1={"OFFLine"}>}"Operating Time [h]")/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>}"Operating Time [h]")
))*sum({$<Station={"BMM"}>}"Operating Time [h]")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL <WeekNumber> "Operating Time [h]"),F1,Line,WeekNumber))))

OEE (1).qvw 415.2 K

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 10, 2017 5:25 AM (in response to Sunny Talwar )Hi Sunny,
Yes, it works! Could you please explain what this first "IF" condition here about dimensionality does here?
Thank you.

Re: Aggr function within a sum function
Sunny Talwar Dec 10, 2017 7:43 AM (in response to Apoorva Dhulehole)Check here to see how dimensionality works

Re: Aggr function within a sum function
Apoorva Dhulehole Dec 10, 2017 11:18 PM (in response to Sunny Talwar )Sure. Thanks!









