topic Re: Aggr function within a sum function in QlikView

Aggr function within a sum function

apoorvasd — Mon, 04 Dec 2017 09:39:56 GMT

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.

Re: Aggr function within a sum function

apoorvasd — Tue, 05 Dec 2017 04:18:05 GMT

Anybody had a chance to look into this?

Thank you.

Re: Aggr function within a sum function

sunny_talwar — Tue, 05 Dec 2017 12:39:55 GMT

Please share a sample to check this out

Re: Aggr function within a sum function

apoorvasd — Wed, 06 Dec 2017 04:50:09 GMT

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.

Re: Aggr function within a sum function

sunny_talwar — Wed, 06 Dec 2017 09:38:11 GMT

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

Re: Aggr function within a sum function

apoorvasd — Wed, 06 Dec 2017 09:57:01 GMT

Hi Sunny,

Perfect! You are a GENIUS

Thanks a lot!

Re: Aggr function within a sum function

apoorvasd — Fri, 08 Dec 2017 10:07:20 GMT

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.

Re: Aggr function within a sum function

sunny_talwar — Fri, 08 Dec 2017 12:08:46 GMT

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

apoorvasd — Fri, 08 Dec 2017 12:22:02 GMT

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.

Re: Aggr function within a sum function

sunny_talwar — Fri, 08 Dec 2017 12:28:17 GMT

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

Re: Aggr function within a sum function

apoorvasd — Sun, 10 Dec 2017 10:25:24 GMT

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 — Sun, 10 Dec 2017 12:43:04 GMT

Check here to see how dimensionality works

How to use - Dimensionality()

Re: Aggr function within a sum function

apoorvasd — Mon, 11 Dec 2017 04:18:35 GMT

Sure. Thanks!