14 Replies Latest reply: Apr 13, 2016 5:23 AM by Stefan Wühl

# Sum of Weighted Average

Hi,

I have the following problem.

I have a chart in QV which looks like:

Avg.Time Total (Min.) is a weighed average which is calculated like:

[Avg.Duration(Min.)] * [#Orders Per Activity] / [# Orders per Stage]

Sum of Avg.Time Total (Min.) is 100.5.

Now I want to create a Bar Chart which will show sum of Avg.Time Total(Min.) per Stage only which looks like :

The value of this bar should be Sum of Avg.Time Total (Min.)  -  100.5.

What formula should I use?

• ###### Re: Sum of Weighted Average

Something like this?

Sum([Avg.Duration(Min.)] * [#Orders Per Activity]) / [# Orders per Stage]

or

Sum([Avg.Duration(Min.)] * [#Orders Per Activity] / [# Orders per Stage])

• ###### Re: Sum of Weighted Average

Hi Jonathan,

it is problematic as [# Orders per Activity] is not calculated in script.

It is calculated in the first chart like:

count(distinct order_unit_id).

As the first chart is per Activity, so it works.

So what solution for this expression I can have in the bar chart, as bar chart is not per Activity, but per Stage?

• ###### Re: Sum of Weighted Average

Hi,

Sum([Avg.Duration(Min.)] * [#Orders Per Activity]) /  (Total [# Orders per Stage])

• ###### Re: Sum of Weighted Average

Try something like

=Sum(

Aggr(

Sum([Avg.Duration(Min.)]) * Count(DISTINCT order_unit_id) / Count(DISTINCT TOTAL<Stage> order_unit_id)

,Stage, Acitivity)

)

• ###### Re: Sum of Weighted Average

Hi,

unfortunately it did not work, chart shows "no data".

• ###### Re: Sum of Weighted Average

Have you replaced the field names with your correct, case sensitive field names from your data model?

Stage, Activity, order_unit_id, [Avg.Duration(Min.)] etc.?

• ###### Re: Sum of Weighted Average

Hi,

my expression looks like:

=Sum(
Aggr(
Sum(avg(ACTIVITY_AGING_HOURS*60)) * Count(DISTINCT ORDER_UNIT_ID) / Count(DISTINCT TOTAL <Stage_Sp> ORDER_UNIT_ID)
,
Stage_Sp, Spanish)
)
. Why do you think it does not work?

• ###### Re: Sum of Weighted Average

Because you are embedding an aggregation into another one:

Sum(avg(ACTIVITY_AGING_HOURS*60))

Does this work in your original chart?

• ###### Re: Sum of Weighted Average

Hi,

no.

That's why I need some aggregative function.

• ###### Re: Sum of Weighted Average

Well, basically you need to replicated your expressions you are currently using on the detailed line level in your chart in the advanced aggregation expression:

What expression are you using for the avg duration on activity level? Avg(ACTIVITY_AGING_HOURS*60)?

Then try

=Sum(
Aggr(
avg(ACTIVITY_AGING_HOURS*60) * Count(DISTINCT ORDER_UNIT_ID) / Count(DISTINCT TOTAL <Stage_Sp>ORDER_UNIT_ID)
,
Stage_Sp, Spanish)
)

AGGR...

Pitfalls of the Aggr function

but also

Average – Which average?

• ###### Re: Sum of Weighted Average

Hi Stefan,

I replaced sum(avg) with aggregative function and it worked:

=Sum(
Aggr(
sum(aggr(avg(ACTIVITY_AGING_HOURS*60),Spanish,Stage_Sp)) * Count(DISTINCT ORDER_UNIT_ID) / Count(DISTINCT TOTAL <Stage_Sp> ORDER_UNIT_ID)
,
Stage_Sp, Spanish)
)

Appreciate your help a lot!!!

• ###### Re: Sum of Weighted Average

Inna Shnaiderman wrote:

Hi Stefan,

I replaced sum(avg) with aggregative function and it worked:

=Sum(
Aggr(
sum(aggr(avg(ACTIVITY_AGING_HOURS*60),Spanish,Stage_Sp)) * Count(DISTINCT ORDER_UNIT_ID) / Count(DISTINCT TOTAL <Stage_Sp> ORDER_UNIT_ID)
,
Stage_Sp, Spanish)
)

Appreciate your help a lot!!!

That's good to hear.

I would assume that this expression could be simplified, though. But to give you any specific advise, we need to know more about your data model and requirements.

If your issue is resolved, please consider closing this thread by marking any helpful or correct answer.

Thanks,

Stefan

• ###### Re: Sum of Weighted Average

Stefan's expression looks OK, but if it does not work, I suggest that you upload a sample qvw with some representative data and specify exactly what result would be required.

Check that the field names are all correct (case is significant). Otherwise, the problem might be in the data model.

• ###### Re: Sum of Weighted Average

Ah, is your avg duration min. also an expression label in your chart, not a field?

It's hard to give you any specific advise if we don't know how the numbers are actually calculated

--> dimensions, expressions, data relations are essential to know