26 Replies Latest reply: Sep 7, 2011 5:59 PM by Stefan Wühl RSS

    Average of a field on weekdays

      Hi,

       

      I have a calendar module which gives me month, weekdays, etc etc..

      I have another table which connects to the calendar module by date.

       

      Now in the table, I have a field called [enrollment count], which has some data filled for every day.

       

      I have a chart which gives me a sum of the [enrollment count] day wise: i.e. Monday, Tuesday, Wednesday, etc.. I used the expression sum([expression count])

       

      Now instead of sum, I want an average of the [enrollment count] day wise. So if there are five sundays in a month it should take the sum of all the five values and then divide it by 5. I used the expression avg([expression count]), but the result is not correct.

       

      Please suggest where I am going wrong.

        • Average of a field on weekdays
          Stefan Wühl

          Maybe like

          =avg(aggr(sum([expression count]),Weekday,Month))

           

          In a table with dimensions Weekday and Month.

           

          Hope this helps,

          Stefan

            • Average of a field on weekdays

              Hi Stefan,

               

              The above expression gives me the sum of [enrollment count] on weekdays. It is not giving an average.

              Any other suggestion?

               

              Regards,

              Rajiv

                • Average of a field on weekdays
                  Stefan Wühl

                  Ah yes, I think my expression is more useful for totals.

                   

                  Have you tried a plain

                   

                  =sum([expression count]) / count ([expression count])

                   

                  ?

                  In a table with dimensions Year, Month, Weekday, I think this should give you the average count per weekday in a given month.

                    • Average of a field on weekdays
                      Stefan Wühl

                      Me again,

                       

                      I think you should change above in

                      =sum([expression count]) / count (distinct Date)

                       

                      thus summing the values per Weekday / Month / Year, but then dividing by the number of distinct dates.

                        • Average of a field on weekdays

                          Hi Stefan,

                           

                          Thanks for the response, but want to understand the working..

                           

                          I want to understand the difference between the three expressions:

                           

                          1) avg([expression count])

                          2) sum([expression count]) / count ([expression count])

                          3) sum([expression count]) / count (distinct Date)

                           

                          The 1st and the 2nd expression give the same result.

                           

                          Cheers,

                          Rajiv

                            • Re: Average of a field on weekdays

                              Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                              23.09.2011.

                               

                              Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                               

                               

                               

                              Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                              Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                              • Average of a field on weekdays
                                Stefan Wühl

                                Hi Rajiv,

                                 

                                IMHO, the expressions  1) and 2),  (i.e. avg([Field]) and sum([Field]) / count([Field]) )  are identical (so 2) is just how the function avg is calculated with more granular functions).

                                 

                                3) is also like an average, but it uses a different count number to divide by.

                                While 1) and 2) iterate over the records restricted by the charts dimensions and do a sum and count of all these records (which would give you an average too low in your case, give that you could have multiple records per weekday in any given week), 3) will divide only by the number of distinct Dates.

                                 

                                For example, if you have multiple records for today 2011-09-05, like

                                recNo, Value, Date

                                1, 10, 2011-09-05

                                2, 10, 2011-09-05

                                3, 10, 2011-09-05

                                 

                                 

                                1) and 2) will calculate:

                                sum(Value) / count(Value) = 30 / 3 = 10

                                 

                                while 3) calculates:

                                sum(Value) / count (distinct Date) = 30 /1 = 30

                                 

                                It's always helpful for learning to put the expressions in parts and add the parts as separate expressions to your table, so you will learn how QV does the calculations taking the dimensions / selections into account.

                                 

                                Hope this helps,

                                Stefan

                                  • Re: Average of a field on weekdays

                                    Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                    23.09.2011.

                                     

                                    Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                     

                                     

                                     

                                    Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                    Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                    • Re: Average of a field on weekdays

                                      Thanks for the post Stefan,

                                       

                                      I still have a doubt..

                                      My data has only one record per weekday. Meaning for a particular date I have only one record.

                                       

                                      Yet when I used expressions 1 and 2, it gave me a very low value. Expression 3 ofcourse gave me the correct value.

                                       

                                      Your example makes sense, when there are multiple records for a particular date. But when there is a single record per date, and yet you get a lower value, it means that qlikview is doing something else which I am not comprehending..

                                       

                                      Your inputs...

                                       

                                      Cheers,

                                      Rajiv

                                        • Re: Average of a field on weekdays

                                          Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                          23.09.2011.

                                           

                                          Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                           

                                           

                                           

                                          Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                          Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                          • Re: Average of a field on weekdays
                                            Stefan Wühl

                                            Hi Rajiv,

                                             

                                            count([Field]) must be different from count(distinct Date) then. Have you looked at the numbers of

                                            count([Field])

                                            count(Date)

                                            count(distinct Date)

                                            (put as expression in your table)?

                                             

                                            You could add Date as dimension to your table after your Weekday.

                                             

                                            I would recommend selecting the date and trying to look at the record level, which records are selected (by looking at a unique ID, maybe?). And keep in mind, that e.g. multiple identical records are not shown multiple e.g. in table box.

                                             

                                            Regards,

                                            Stefan

                                              • Re: Average of a field on weekdays

                                                Thanks Stefan,

                                                 

                                                Really helpful... There indeed was multiple records for a particular date, which I had overlooked!! And hence your earlier example stands as the explanation for the lower value that I was getting.

                                                 

                                                Cheers,

                                                Rajiv

                                                  • Re: Average of a field on weekdays

                                                    Next requirement:

                                                     

                                                    Now that I have the average of the enrollment count day wise, I want to remove the outliers so that the average value is not skewed.

                                                     

                                                    For example in the 5 sundays of a month: the enrollment count could be 25,24,23,22,01.

                                                    Now I don't want my average to consider 01.

                                                     

                                                    The average that I am getting now(with current expression) is 19((25+24+23+22+01)/5)

                                                    I should ideally get 23.5((25+24+23+22)/4)

                                                     

                                                    Any suggestions on how to go about this.

                                                     

                                                    Regards,

                                                    Rajiv Maskara

                                                      • Re: Average of a field on weekdays
                                                        Stefan Wühl

                                                        IMHO, removing outliers is kind of dangerous, especially with low number of data points (like sundays in a month, max 5).

                                                         

                                                        But anyway, what do you consider an outlier? Is there any rule (e.g. distance from mean)?

                                                        Or do you just wanna skip the min and max values?

                                                          • Re: Average of a field on weekdays

                                                            I have the enrollment count data, which is a daily event. But then on national holidays, there are no or very little enrollments.

                                                            So, for example, if a state has a daily average enrollment count of around 50000, on national holidays this count would fall to say around 10000...

                                                             

                                                            The numbers can vary from state to state.. So for a different state, the daily enrollment count could be only around 7000 and on national holidays, this could fall to say 1000...

                                                             

                                                            This is the situation that I have with me... I understand that removing outliers with so few data points could be a little risky, but it tends to skew the average quite a lot, precisely because there are so few data points.

                                                             

                                                            Any suggestions now??

                                                              • Re: Average of a field on weekdays

                                                                Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                                23.09.2011.

                                                                 

                                                                Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                                 

                                                                 

                                                                 

                                                                Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                                Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                                • Re: Average of a field on weekdays
                                                                  Stefan Wühl

                                                                  Hi Rajiv,

                                                                   

                                                                  the cleanest way would be to add the information about national holidays to your data model (for example by adding a holiday flag to the master calendar per state) and then use this information to filter the data to do calculations on. Thus, you could state "Removing data points for national holidays" and everybody could accept or cry out, but knows what's going on.

                                                                   

                                                                  I think you don't want to do that.

                                                                   

                                                                  Hm, there are a lot of different criteria to label a data points as "outlier", like percentage of average value, distance to average by standard deviation, min/max, percentile etc.

                                                                  Each criteria is somewhat abitrary and must be set "by experience".

                                                                   

                                                                  In Rob's QlikView cook book, there is an example for some of these methods, I recommend having a look into that.

                                                                   

                                                                  I also created an example that uses 20% of the average weekday value as threshold to determine if a certain datapoint should pass (and I do that only once, not iterative).

                                                                  In attached sample, I created a simple data model for weekday values and tried to create some outliers also.

                                                                   

                                                                  The resulting formula to calculate the corrected average (with above defined average) looks like

                                                                   

                                                                   

                                                                  =sum(if(aggr(sum(Value),Date) >= aggr(NODISTINCT sum(Value) /count(distinct Date), Month, Weekday) /5 , aggr(sum(Value),Date))) /

                                                                  count( Distinct if(aggr(sum(Value),Date) >= aggr(NODISTINCT sum(Value) /count(distinct Date), Month, Weekday) /5 , Date))

                                                                   

                                                                  Pretty awful (and some of this is due to the special average we use) and possibly one can simplify that.

                                                                   

                                                                  I use this just for a demonstration and playground, if you want to use this, one could also think about putting some of these stuff in the script.

                                                                   

                                                                  Hope this helps,

                                                                  Stefan

                                                                • Re: Average of a field on weekdays

                                                                  Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                                  23.09.2011.

                                                                   

                                                                  Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                                   

                                                                   

                                                                   

                                                                  Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                                  Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                                • Re: Average of a field on weekdays

                                                                  Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                                  23.09.2011.

                                                                   

                                                                  Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                                   

                                                                   

                                                                   

                                                                  Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                                  Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                                • Re: Average of a field on weekdays

                                                                  Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                                  23.09.2011.

                                                                   

                                                                  Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                                   

                                                                   

                                                                   

                                                                  Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                                  Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                                • Re: Average of a field on weekdays

                                                                  Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                                  23.09.2011.

                                                                   

                                                                  Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                                   

                                                                   

                                                                   

                                                                  Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                                  Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                          • Re: Average of a field on weekdays

                                                            Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                            23.09.2011.

                                                             

                                                            Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                             

                                                             

                                                             

                                                            Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                            Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                          • Re: Average of a field on weekdays

                                                            Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                            23.09.2011.

                                                             

                                                            Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                             

                                                             

                                                             

                                                            Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                            Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                          • Re: Average of a field on weekdays

                                                            Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                            23.09.2011.

                                                             

                                                            Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                             

                                                             

                                                             

                                                            Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                            Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

                                                        • Re: Average of a field on weekdays

                                                          Ich werde ab  05.09.2011 nicht im Büro sein. Ich kehre zurück am

                                                          23.09.2011.

                                                           

                                                          Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

                                                           

                                                           

                                                           

                                                          Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

                                                          Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!