9 Replies Latest reply: Nov 22, 2017 11:37 AM by David Forest RSS

    Set Analysis

    Michael Rainey

      I am trying to come up with a way to do the 90 day average for each weekday.  This works for the 90 day average but I'm not sure how I am going to work in the by weekday.  Any ideas?

       

      avg( {<CalendarDate = {"$(='>' & $(v90Days))"}>} Sales)

        • Re: Set Analysis
          Sunny Talwar

          May be create a weekday flag in the script....

          If(Match(WeekDay(CalendarDate), 'Mon', 'Tue', 'Wed', 'Thu', 'Fri'), 1, 0) as WeekDayFlag

           

          and then this

          Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDayFlag = {1}>} Sales)

            • Re: Set Analysis
              Michael Rainey

              Its not just weekdays.  I'm trying to get the average sales of Mondays for the past 90 days and compare it to Yesterday average sales.  Same with Tuesday, Wednesday, ect.

               

              So if I have a sales person list of sales for the past year I want to be able to compare each Monday to the past 90 days of Mondays.  Same thing with the rest of the days of the week.

               

              The only thing I came up with is doing some funky stuff on the back end that compares multiples of 7 for the past 7 days while today()-CalendarDate < 91, then I mapped it to the data set.  That adds 10 mins to the load time and I was trying to come up with another way.

                • Re: Set Analysis
                  Sunny Talwar

                  What is the kind of chart you are using? Do you have a sample or images to show what you are after?

                    • Re: Set Analysis
                      Michael Rainey

                      I am using a Pivot Table.  Rows are Team, Account Executive, Date.

                       

                      Unfortunately I can not give you a screen shot because of the customer data.

                        • Re: Set Analysis
                          Michael Rainey

                          This would work if I was only comparing Mondays, but I'm comparing Yesterday or all the days in the last Quarter to the 90 day average.

                           

                          We have high performing days and low performing days and its not fair to compare a high performing day to a low performing day and the average of the two doesn't give us much of a comparison either.  So comparing each day to itself is what we are looking for but its proving difficult to figure out.

                            • Re: Set Analysis
                              David Forest

                              Not sure exactly what you are trying to do, but the expression could be altered to

                              Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDay = {$(WeekDay(Today()-1)}>} Sales)


                              to have it evaluate what day yesterday was. Of course this all depends on other current selections.

                              Other solutions could involve putting the WeekDay field in a Filter box to allow selection of the actual day...

                               

                              To receive additional feedback it is helpful to supply example data with the expected outcome.

                                • Re: Set Analysis
                                  Michael Rainey

                                  Final goal is to be able to look at past 30 to 90 days worth of sales per AE() per Customer and compare those days to the Average over the past 90 days.  I'll be displaying it in a Pivot table and if the sales are down 10% or 25% then the Cell will be highlighted.  Then they can look into that day to see where the AE can improve.

                                   

                                  Most of the time they are looking at yesterday, but they could be looking at the past 90 days or even more if they wanted too. 

                                   

                                  I am trying to come up with a way to display the average 90 day per day of the week.  Does that make sense?

                          • Re: Set Analysis
                            David Forest

                            Extending Sunny's suggestion, create another field in your Calendar that contains the Day,

                            WeekDay(CalendarDate) As WeekDay


                            and then this

                            Avg({<CalendarDate = {"$(='>' & $(v90Days))"}, WeekDay = {'Mon'}>} Sales)