4 Replies Latest reply: Oct 11, 2010 1:09 PM by Karl Pover RSS

    Best Practices for comparing month to date data

      Hello Everyone,

      Wanted to put this topic out there to see how everyone has been or would approach this....

      When doing month to date comparisons over past years, in order to show a fair comparison, only business days should be taken into account.

      How is it that everyone only counts business days?


      To complicate my issue, we are open Monday to Friday all year and Saturdays about 5 months a year.

        • Best Practices for comparing month to date data
          Karl Pover

          When I think about what you're looking for, I don't think I would use the month-to-date concept alone. If you consider for example that this October there are 5 sundays and last October there were 4 sundays, the business could have increased slightly and still sold less this October compared to last October. At first this sounds like poor analysis, but let's get a little more perspective.

          A better analysis could be average sales per day in a month. This is easy to calculate given you only have sales on business days then you would divide total sales of the month so far by a count distinct of the number of days that at least one invoice was registered. In this way you can see if you business is growing regardless of the number of business days in the month. However what if you sell better on Friday's than Monday's and you have more Friday's in a month than Monday's? That doesn't seem like perfect analysis either.

          So, we could also break down the average sales per day by each weekday to get an idea if you're selling better on the Friday's of this month compared to the Friday's of the same month last year, but what-if a holiday fell this month on a Friday when last year it fell on a Sunday? That same problem would occur if you base the month-to-date concepto on the same number of business days. So. nothing seems perfect at this point.

          There might be more indicators to evaluate a month and I'm not a profesional statistician, but if we put these 3 indications together in the same dashboard, it would give the user a pretty good story about what's happening in the month and along with year-to-date information and his or her gut feeling, the user should have a pretty good idea of how the business is doing.

          Well, that's my opinion anyway.


            • Best Practices for comparing month to date data

              You make some very good points. Ideally I would want to compare business days completed only. I am not as worried about whether it is a monday or friday. What I would want to know is month to date GP based on business days compared to last years month to date GP based on the same amount of business days. What do you think is the best way to accomplish this?

                • Best Practices for comparing month to date data
                  Jonathan Dienst


                  Qlikview has three built in functions for working day calculations - see a short series on how to use these functions here.

                  One problem is although these functions allow you to add non-working days (for public holidays), they assume a work week that runs from Monday to Friday, and have no built in way for adding extra working days, so you will have to modify the output to account for your working Saturdays.

                  My suggestion would be that you add a working days field to you load script that shows (among others):

                  • Date
                  • Month
                  • Year
                  • Working day in month

                  For non-working days, the working day field could be set to the previous working day, the next working day or zero, depending on your requirements.


                  • Best Practices for comparing month to date data
                    Karl Pover

                    Here's a quick idea. Not tested.

                    1. Load a list of working saturdays in an in-line table or from Excel

                    2. Load distinct just the date from your transactions and order it by date. This table will be link to the transactions table.

                    3. In this date table evaluate the date of the document like below:

                    if(month(Trx_Date) = month(previous(Trx_Date) and (weekday(Trx_date)='Sunday' or not exists(List_Saturdays,Trx_date)), previous(Business_Day_No),

                    if(month(Trx_Date) = month(previous(Trx_Date) ,previous(Business_Day_No)+1),

                    if(month(Trx_Date) <> month(previous(Trx_Date) and (weekday(Trx_date)='Sunday' or not exists(List_Saturdays,Trx_date)),0,1) as Business_Day_No

                    4. Then in the graph you could use set analisis or a sum-if to analyze the same number of business days as if Business_Day_No were the real day of the month.