Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

apoorvasd
Contributor II

Aggr function within a sum function

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

Two weeks.PNG

One week.PNG

Any suggestion?

Thank you.

1 Solution

Accepted Solutions

Re: Aggr function within a sum function

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


Capture.PNG

12 Replies
apoorvasd
Contributor II

Re: Aggr function within a sum function

Anybody had a chance to look into this?

Thank you.

Re: Aggr function within a sum function

Please share a sample to check this out

apoorvasd
Contributor II

Re: Aggr function within a sum function

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

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


Capture.PNG

apoorvasd
Contributor II

Re: Aggr function within a sum function

Hi Sunny,

Perfect! You are a GENIUS

Thanks a lot!

apoorvasd
Contributor II

Re: Aggr function within a sum function

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

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
Contributor II

Re: Aggr function within a sum function

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

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

Capture.PNG

Community Browser