Qlik Community

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

14 Replies
MVP
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
sureshqv
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
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
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
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
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?

Community Browser