Average calculation

[#1]

Hi to all,

I have the following expression in a line chart:

fabs(sum((if(SettimanaSelezione = SettimanaVendite, QtaVenduto, 0))))

/

(sum((if(SettimanaSelezione > SettimanaVendite, QtaVenduto, 0)))

+

sum((if(SettimanaSelezione > SettimanaVendite, QtaCaricata, 0)))

+

sum((if(SettimanaSelezione > SettimanaVendite, QtaTrasf, 0)))

)

where SettimanaVendite is the number of a week in a special time period (i.e it's different from the calndar week number) and SettimanaSelezione is the same number range but loaded in a data island table. When I ask to Qlik to put the Average line in the graph I get a result that is total amount for each week divided by the number of the weeks selected in the graph. What I need is to calculate the average value, like the value calculated by Qlik, inside a text object. Of course we can assume that the week range is fixed, for example 3 weeks.

Thanks!

Andrea

[johnw]

I'm not sure I understood, but maybe using "total" would do what you want?

fabs(sum(total if(SettimanaSelezione = SettimanaVendite, QtaVenduto)))
/ ( sum(total if(SettimanaSelezione > SettimanaVendite, QtaVenduto))
+ sum(total if(SettimanaSelezione > SettimanaVendite, QtaCaricata))
+ sum(total if(SettimanaSelezione > SettimanaVendite, QtaTrasf)))

Edit: And can't we simplify to this?

fabs(sum(total if(SettimanaSelezione = SettimanaVendite, QtaVenduto)))
/ sum(total if(SettimanaSelezione > SettimanaVendite, rangesum(QtaVenduto,QtaCaricata,QtaTrasf)))

[#3]

Hi John,

not really. I made a small Qlik where you can see that the value in the text object is different from the average value shown in the graph.

Thanks!

Andrea

[brenner_martina]

Hi,

the difference is in that:

(sum(sold)/sum(delivered)) = 0,95305832147937

and

(sum(sold/delivered)) = 6,4681066233577

that's why the average is also different

[#5]

Hi Martina,

ok, unfortunatly the data are more complex than my small example. In reality I have:

=(fabs(sum(if((SettimanaVendite >= $(Var_PrimaSettimana) and SettimanaVendite <= $(Var_UltimaSettimana)), QtaVenduto, 0))

/

(sum({<SettimanaVendite = {'<=$(=$(Var_UltimaSettimana))'}>} (QtaVenduto))

+

sum({<SettimanaVendite = {'<=$(=$(Var_UltimaSettimana))'}>} (QtaCaricata))

+

sum({<SettimanaVendite = {'<=$(=$(Var_UltimaSettimana))'}>} (QtaTrasf)))

))

/ ($(Var_UltimaSettimana) - $(Var_PrimaSettimana)+1)

So any idea on what I can do?

Thanks!

[johnw · Accepted solution]

Ah, I should have realized that when you said you put an average line in, you were just checkmarking the average trendline, not adding one yourself.

Now, the default average line in a chart is the average of the point values across the dimension. If you want, think of it as a visual average of the displayed points. For your example chart, you can duplicate this average by using this in a text box:

avg(aggr(sold/delivered,week))

It's the average of the ratios of sold to delivered across weeks. It is correct for what it is. It is not, however, what most people mean if they ask for an overall ratio. When someone wants the overall ratio, they typically mean IGNORING the dimension field. So they don't want you aggregating by weeks, but simply getting the ratio for the entire data set. That would be this:

sum(sold)/sum(delivered)

You would NOT divide this by 6. Weeks have nothing to do with this approach. It doesn't matter how many weeks are in the data set any more than it matters how many customers or product types you have.

Now, these two approaches produce two different answers. They SHOULD produce two different answers. And both answers are correct. They just answer different questions.

If you want to know the average of the ratios, then the average line in the chart is just fine.

If you want to know the overall ratio, and display it in your chart, you'll need to add a separate expression for that. However, since expressions in a chart are evaluated in the context of the dimension, you can't just slap avg(aggr(sold/delivered,week)) into the chart. You'll actually get the exact same line/curve as the regular data points. Instead, you need to tell the chart to ignore the dimension. You do that by using the "total" keyword. For your sample data set, like this:

sum(total sold)/sum(total delivered)

And for your actual data set, the way I already posted. Add it as a second expression. I didn't mean for it to replace your first expression.

Edit: Added the other sort of average line to your example file. See attached.

[#7]

Hi John,

first thanks for the time spending on looking for a solution.

As you well know is always quite hard to explain all the different part of the scenario, so I try to make it as simple as possible, but sometimes this don't give the necessary overview.

In fact the final solution it's coming from your suggestion and the formula is:

=avg({<SettimanaSelezione={'>=$(Var_PrimaSettimana)'}*{'<=$(Var_UltimaSettimana)'}>}

aggr(

fabs(

sum({<SettimanaSelezione={'>=$(Var_PrimaSettimana)'}*{'<=$(Var_UltimaSettimana)'}>}

(if(SettimanaSelezione = SettimanaVendite, QtaVenduto, 0)))

/

(sum({<SettimanaSelezione={'>=$(Var_PrimaSettimana)'}*{'<=$(Var_UltimaSettimana)'}>}

(if(SettimanaSelezione > SettimanaVendite, QtaVenduto, 0)))

+

sum({<SettimanaSelezione={'>=$(Var_PrimaSettimana)'}*{'<=$(Var_UltimaSettimana)'}>}

(if(SettimanaSelezione > SettimanaVendite, QtaCaricata, 0)))

+

sum({<SettimanaSelezione={'>=$(Var_PrimaSettimana)'}*{'<=$(Var_UltimaSettimana)'}>}

(if(SettimanaSelezione > SettimanaVendite, QtaTrasf, 0))))

)

,SettimanaSelezione

)

)

The set analysis makes the formula working fine as a reference line in the graph independently from the week selected in the graph but reduce the data in the range selected with the slider.

Thanks again for your help!

Andrea