Sum of Weighted Average

[#1]

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?

[swuehl]

Because you are embedding an aggregation into another one:

Sum(avg(ACTIVITY_AGING_HOURS*60))

Does this work in your original chart?

[#3]

Hi,

no.

That's why I need some aggregative function.

[#4]

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

[swuehl]

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

See also

AGGR...

Pitfalls of the Aggr function

but also

Average – Which average?

[swuehl · Accepted solution]

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