# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
MVP

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

Thanks,

Stefan

14 Replies
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Esteemed Contributor III

## Re: Sum of Weighted Average

Hi,

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

Not applicable

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

MVP

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

)

Not applicable

## Re: Sum of Weighted Average

Hi,

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

MVP

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

MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP

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

Not applicable

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