topic AVG Function in QlikView

AVG Function

Wed, 21 Apr 2010 13:05:52 GMT

Dear Guys,

I'm getting crazy with an easy request, but I'm new to qlikview.

I've OrderTable with Header and Position and I'm just calculating the lead time (GoodIssueDate - InsertOrderDate) grouping it by country.

Basically I need to have an avg lead time calculation :

And I put an expression Avg(GoodIssueDate - InsertOrderDate) and it works if I select "Average" under "Total Mode" on the Expressions tab

If I select Total Expressions it give me wrong result. Problem is that if I have all the line orders items result is ok, if I have it grouped by country it give me a wrong result.

Any Idea ?

Thanks

Sergio

AVG Function

sathishkumar_go — Wed, 21 Apr 2010 14:06:33 GMT

Hi Sergio,

Could u plz explain me abu ur fields. i.e goodissuedate and inserorderdate both 2 r date format. and also same format means dd/mm/yyyy.

-Sathish

AVG Function

Fri, 30 Apr 2010 10:50:39 GMT

Yes both are the same.

Basically what I don't understand is the difference result I get with "Total Expression" undel Total Mode compared to "Average"....

If I extract record from the table graph in excel and I'm doing manual calculation it correspond to the "average" way.

So it seems that "Total Expression" calculate something more I can't see in the graph table (fields in other table...). Any idea about different behavior

Sergio

AVG Function

Fri, 30 Apr 2010 13:09:42 GMT

As I understand,

You should use SUM in order to show the results for "group by country".

Try This

Avg(SUM(GoodIssueDate) - SUM(InsertOrderDate))

And please give feedback that it works or not

AVG Function

Sun, 02 May 2010 09:08:59 GMT

Dear Tu?ba,

It doesn't works, it get me out zero, as result.

I've posted my file in http://community.qlik.com/forums/t/28543.aspx

Thanks for any support

Sergio

AVG Function

Sun, 02 May 2010 18:15:42 GMT

Sergio,

Use this expression

if(isnull(GoodIssueDate - InsertOrderDate),0,GoodIssueDate - InsertOrderDate)

And set total mode as Average of rows and number format to integer.

AVG Function

Mon, 03 May 2010 10:55:51 GMT

Dear dragonauta,

It doesn't provide me the right result. If I try to collect information by region or whatever (look to my file) it provide me the wrong result. It seems that collecting by some dimensions, system is using others dimensions that give me wrong avg.

Thanks

AVG Function

Mon, 03 May 2010 13:05:22 GMT

Your data contains nulls for InsertOrderDate and also for GoodIssueDate, also for some entries you have no day difference between the two.

Is it correct that you want only an average duration of entries where both the dates are set?

For selection year 2010 you have 83 rows which have both dates set. The sum of delay days is 265, average 265/83=3.19

I see that you get this for the formula Avg(GoodIssueDate - InsertOrderDate) with Total Mode set to "Expression Totals". Do you also consider this the right value?

Saw that we end up in differences between AVGTOT and AVGAVE only when filtering on the OrderDate. If the filter is set on either InsertOrderDate or GoodIssueDate the values match.

I wasn't however able to see how AVGAVE is calculated for the OrderDate selection where we can have either InsertOrderDate and GoodIssueDate being NULL.

Juerg

AVG Function

Mon, 03 May 2010 13:30:07 GMT

Hi,

Yes, it's correct, I want to have an average duration only when both dates are set, in these case the right value is not AVGTOT (Total mode set to "Expression Total"), but rather AVGAVE (total mode set to "Avg of rows).

In this case, the right calculation is AVGAVE 'cause the right sum is 247, divided by 80 (the row where both GoodIssueDate and InsertOrderDate has a value) give me 3,0875.

If u compare column AVGAVE with value in column "Somma Giorni" u will see that in some circumstances u have wrong value in "Somma Giorni". It's double probably due to others dimensions that affect the whole result.

What finally I'm looking for is to have the right calculation (in this case 3,0875) no matter which dimensions I've grouped by (see on the graph above, by MACROAREA_DESCR)

I hope it clarify my doubt.

I believe some other dimensions (not displayed) are creating confusion providing wrong figures

Thanks

Sergio

AVG Function

Mon, 03 May 2010 14:35:28 GMT

Hi Sergio

Can you explain why you are using SUM() for the SommaGiorni detail formula? This doubles in one case the 7, in the other the 9 days.

I have tried to look into the tables but can not find an explanation for the double count.

Juerg

AVG Function

Tue, 18 May 2010 08:21:41 GMT

Dear all,

Thanks for all your feedback. I've solved it doing the calculation on load side, so it avoid any duplication and dirty data.

Thanks

Sergio