6 Replies Latest reply on Sep 30, 2016 2:23 AM by JAGADEESH KUMAR

    how to calculate last week sales for every month?

    Venkatesh Pilla

      Can Any one give solution for  finding last week sales for every month in every year   for superstore data??

       

       

       

      For example, For Jan-2010  Last week falls from 31-Jan-2010 to 6-Feb-2010...I want sales between these Days

        • 1. Re: how to calculate last week sales for every month?
          Ivan Young

          Hi Venkatesh,

          You can follow the steps below to find the maximum week for every year/month by the following method. Keep in mind the last week of the month may be a partial week and have low sales as a result.

           

          1.  Create a custom date for months.

           

           

          2.  Create a field to extract the week # - week: DATEPART('week',[Order Date])

           

          3. Create your to filter to return the max week every year/month -  Max Week Filter: { FIXED [Order Date (Months)]: MAX([week])} = [week]

           

          4.  Drag Max Week Filter to filters and set to True.

           

          Let me know if you have any questions

           

          Regards,

          Ivan

           

          • 2. Re: how to calculate last week sales for every month?
            JAGADEESH KUMAR

            Hello  Ivan Young,

                               In the given formula, u have only considered the last week days of each month of every year. i mean in JAN 2010 the last week is the combination of JAN and FEB. that means. from 31st JAN to 6 FEB 2010. but the formula u gave only find the value for that last week days of that month. i.e., 31st JAN 2010.

            Can u please tell the formula for find the whole week sales ie., from 31st JAN to 6 FEB 2010

            • 3. Re: how to calculate last week sales for every month?
              Ivan Young

              Hi Jagadeesh,

              You'll then need to modify your filter to { FIXED [Year], [week] : MIN({ FIXED [Order Date (Months)]: MAX([week])})} = [week] and set top True.  Check out the attached and let me know if you have any questions.


              Regards,
              Ivan

              3 of 3 people found this helpful
              • 4. Re: how to calculate last week sales for every month?
                JAGADEESH KUMAR

                Hello Ivan Young,

                                  Thank you Iyan Young. I got the solution . thanks for your calculation. I appreciate for quick  reply. But can u please tell me how exact that calculation is working.

                 

                Regards,

                Jagadeesh

                • 5. Re: how to calculate last week sales for every month?
                  Ivan Young

                  Hi Jagdeesh,

                  I'll do my best to explain what is happening in the worksheet.

                   

                  First we start with the raw data, note the last week Jan has $241 in revenue for January and $1203 for February.  We need to combine these numbers which we will do through LOD.

                   

                  We create our first LOD which identifies the Maximum week each month. Max Weeks: { FIXED [Order Date (Months)]: MAX([week])}.  Note that week six has Max Weeks of 6 & 10 associated with it.  We need to create another LOD to find the Min Max Week associated with each week.

                   

                  We create yet another LOD finding the Min Max Week for each week:  Min Max Week: { FIXED [Year], [week] : MIN([Max Week]) }.

                   

                  Then we create a filter where week = Min Max Week., since week numbers will repeat every year we need to add year to the filter.  Min Year Week:  { FIXED [Year], [week] : MIN([Max Week]) } = [week].

                   

                   

                  Now if we remove month from the view we are left with just the last week of each month with full revenue for that week even though it spans multiple months.

                   

                  I hope this helps with understanding how this works.  Let me know if you have any questions.


                  Regards,
                  Ivan

                  3 of 3 people found this helpful
                  • 6. Re: how to calculate last week sales for every month?
                    JAGADEESH KUMAR

                    Hello Ivan,

                                   Thank you so much for reply. one small doubt, for example if you finding the max week of January, the max week is week 6. but how come it will consider both 6 and 10 week. remaining i understood. Really thank you for giving such a nice explanation.

                     

                    With Regards,

                    Jagadeesh.