AVG Function

[#1]

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

[sathishkumar_go]

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

[#3]

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

Sergio,

Use this expression

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

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

[#7]

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

[#8]

Hi

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

[#9]

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

[#10]

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