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?

[jonathandienst]

Something like this?

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

or

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

[Chanty4u]

Hi,

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

[#4]

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?

[swuehl]

Try something like

=Sum(

     Aggr(

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

     ,Stage, Acitivity)

)

[#6]

Hi,

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

[swuehl]

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

[jonathandienst]

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.

[swuehl]

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

[#10]

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?