7 Replies Latest reply: Apr 27, 2012 2:51 AM by matthew thompson RSS

    Average sales per weekday

      Hi,

       

      I'm creating a daily sales report and need to compare today's sales against the same day's average over the last 4 weeks. For instance, today I would show Wednesday's sales, the average of the last 4 Wednesday's sales and the difference between them. I'm currently having issues getting Wednesday's sales to show. I'm using the following set:

       

      sum({<[Day Name] = {'$(=weekday(today(0))'}>} [Sales])

       

      If  I replace the above with today's day, it works fine, but I need something dynamic:

       

      sum({<[Day Name] = {"Wednesday"}>} [Sales])

       


      Once I get this sorted, I'll need to sum the sales from the last 4 Wednesdays. Does anyone know an expression for this?

       

      Any insight would be appreciated. I've attached an example below.

       

      Best,

       

      Matt

        • Average sales per weekday

          Hi,

          just add a second set expression like

           

          sum({$< SalesDate = {'>=$(=AddMonths(Now(0), -1))'}[Day Name] = {'$(=weekday(today(0))'}>} [Sales])

           

          greets,

          Max

            • Average sales per weekday

              Thanks Max, that's very helpful. I still cannot get this part of the expression to work though:

               

              [Day Name] = {'$(=weekday(today(0))'}

               

              If I subsitute {'$(=weekday(today(0))'} for  {"Wednesday"} it works, but I need it to work dynamically. Does anything stand out to you as wrong with this expression?

               

               

              Thanks again for the help.

               

              Matt

               


               


                • Average sales per weekday
                  Masha Aleinikova

                  Check the result of your formula weekday(today(0)) in a text box. If it does not return 'Wednesday', modify environment variable DayNames in the load script.

                  • Average sales per weekday

                    It could be caused by the format within your [Day Name] column. Give it a try with num(weekday(today(0))) or alternatively text(weekday(today(0)))

                     

                    How is your column populated ? If its plain text, make sure that the weekday function returns the same day name format - including probably abbreviations

                      • Average sales per weekday

                        Thanks a lot for the help, really appreciate it. I still had issues getting the weekday expression to work. In the end, I've settled on the following solution to calculate yesterday's sales against the  average for last 4 like weekdays:

                         

                        (sum({$< [Transaction Date] = {'$(=date(today(0)-8))'}>} [Sales]) +

                             sum({$< [Transaction Date] = {'$(=date(today(0)-15)'}>} [Sales]) +

                             sum({$< [Transaction Date] = {'$(=date(today(0)-22))'}>} [Sales]) +

                             sum({$< [Transaction Date] = {'$(=date(today(0)-29))'}>} [Sales]) )/4

                         

                        Best,

                         

                        Matt