10 Replies Latest reply: May 18, 2010 4:21 AM by sergfrac RSS

    AVG Function

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

          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

              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
                  ttierraa

                  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

                      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

                          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

                              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
                                  juergm

                                  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

                                    • AVG Function

                                      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
                                          juergm

                                          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