Aggr function within a sum function

[apoorvasd]

Hello Everyone,

I am using aggr function within sum function as shown in the below expression. The problem is, since I am using "if" statement, the bar chart some how doesn't display the data until I choose a value in field, "F1". After using aggr function this problem is eliminated and the graph displays value even without making a selection in "F1". But now when I choose one particular week, data displayed is right, but if I choose more than one week, expression sums up for both the weeks and displays it as just one bar as shown in the below screenshot. I would want to see % values for how many ever weeks I choose. So here when I choose week1 and week2, % values for both the weeks should be visible in the graph. How do I do this?

Expression:

=(sum(aggr(if(F1='ONLine',sum({$<F1={"ONLine"},Station={"BMM"}>}"Operating Time ")/sum({$<F1={"ONLine"},Station={"BMM"}>}"Planned Prod. Time "),

if(F1='OFFLine',sum({$<F1={"OFFLine"}>}"Operating Time ")/sum({$<F1={"OFFLine"}>}"Planned Prod. Time ")

*sum({$<F1={"OFFLine"}>}"Planned Prod. Time "))/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>} "Planned Prod. Time ")

)*sum({$<Station={"BMM"}>}"Planned Prod. Time ")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL "Planned Prod. Time "),F1,Line)))

Any suggestion?

Thank you.

[apoorvasd]

Anybody had a chance to look into this?

Thank you.

[sunny_talwar]

Please share a sample to check this out

[apoorvasd]

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.

[sunny_talwar · Accepted solution]

Try this

=Avg(Aggr((sum(aggr(if(F1='ONLine',sum({$<F1={"ONLine"},Station={"BMM"}>}"Operating Time ")/sum({$<F1={"ONLine"},Station={"BMM"}>}"Planned Prod. Time "),

if(F1='OFFLine',sum({$<F1={"OFFLine"}>}"Operating Time ")/sum({$<F1={"OFFLine"}>}"Planned Prod. Time ")

*sum({$<F1={"OFFLine"}>}"Planned Prod. Time "))/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>} "Planned Prod. Time ")

)*sum({$<Station={"BMM"}>}"Planned Prod. Time ")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL <WeekNumber>  "Planned Prod. Time "),F1,Line, WeekNumber))), WeekNumber))

[apoorvasd]

Hi Sunny,

Perfect! You are a GENIUS

Thanks a lot!

[apoorvasd]

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.

[sunny_talwar]

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%?

[apoorvasd]

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.

[sunny_talwar]

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 "),

if(F1='OFFLine',sum({$<F1={"OFFLine"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"OFFLine"}>}"Operating Time ")

*sum({$<F1={"OFFLine"}>}"Operating Time ")/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>}"Operating Time ")

))*sum({$<Station={"BMM"}>}"Operating Time ")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL  "Operating Time "),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 "),

if(F1='OFFLine',sum({$<F1={"OFFLine"}>}[Total Pieces Produced]/[Max pc/hr])/sum({$<F1={"OFFLine"}>}"Operating Time ")

*sum({$<F1={"OFFLine"}>}"Operating Time ")/sum({$<Station={"BMM","Assembly","Welding"},F1={"OFFLine"}>}"Operating Time ")

))*sum({$<Station={"BMM"}>}"Operating Time ")/sum({$<Station={"BMM"},Product=,F1=>}TOTAL <WeekNumber> "Operating Time "),F1,Line,WeekNumber))))