11 Replies Latest reply on Aug 4, 2016 9:19 PM by arthi.keat

    Weekly Sales

    arthi.keat

      Hi,

       

      I have data in the following format. Week in Qtr is a string.

       

      Week in QtrSales
      01/2016100
      02/2016500
      03/2016200
      04/201650

       

      I need to show current week and prior week sales number where Current week being 04/2016 and prior week is 03/2016. Can you please help with the formula for both.

        • 1. Re: Weekly Sales
          Stoyko Kostov

          Hi Arthi,

           

          One possibility is the following: Define calculated field "rank" on Week In Qtr as RANK(MIN([Week In Qtr]), 'desc'), and then create a filter using "rank" as range (1, 2) (this gives you only the top 2 values from the column).

           

          I'm attaching a sample workbook.

           

          Hope this answers your question - if not, please let me know your exact desired output, and I'll try to help.

          1 of 1 people found this helpful
          • 2. Re: Weekly Sales
            arthi.keat

            Thank you for your reply.

             

            The complexity I have is, I do not want to filter data as other fields are showing quarter to date values and we cannot filter week.

             

            Is there any formula which gives us the value of max week minus one value.

            For example, according to date 07/27, max week is 04/2016. Now, one minus 04/2016, is 03/2016, i want that value which is 200.

             

            Any idea how we can achieve this..

             

            thank you for your time..

            • 3. Re: Weekly Sales
              Stoyko Kostov

              I see. Try playing with those functions (create calculated fields with those definitions):

               

              DATEPART('week', today()) - this will give you a value for the current week with weeks starting on Monday.

               

              If you want your calculation to be based starting on Jan 1 of the current year, try setting the start of week in DATEPART as follows.

               

              First, calculate the day of week for Jan 1 of the current year as a calculated field named DayOfWeekJan1 and definition

              datename('weekday', dateparse('yyyy-mm-dd', STR(datepart('year', today()))+"-01-01"))

               

              Second, create a parameter off of DayOfWeekJan1 (right-click, create -> parameter)

               

              Third, define calculated field named weekday with definition DATEPART('week', TODAY(), [dayofweekJan1 Parameter]).

              • 4. Re: Weekly Sales
                Stoyko Kostov

                Oh I see you have week in quarter, not week in year. You may have to calculate the difference with the start of the quarter, and divide by 7.

                 

                E.g. -DATEDIFF('week', today(), dateparse('yyyyMMdd', STR(YEAR(today()))+'0701'))

                 

                (you may have to do some if-then-else logic to calculate the start of the quarter that is hardcoded as '0701' above)

                • 5. Re: Weekly Sales
                  Stoyko Kostov

                  This may be the calculation you need:

                   

                  -DATEDIFF('week', today(), dateparse('yyyyMMdd', STR(YEAR(today()))

                  +(IF MONTH(today())<10 THEN '0' ELSE '' END)

                  +STR(FLOOR(MONTH(today())/3)*3+1)

                  +'01'))

                  • 6. Re: Weekly Sales
                    arthi.keat

                    Thank you for your time. I tried your formula but was not able to get the desired result.

                     

                    I have attached a sample workbook. I want help in creating a formula which would give me Sales number in the latest week in quarter, that is 04/2016 in Q316 and the value is 100. And also the prior week which is 03/2016 in Q316 where the value is 400. can you please help take a look..

                     

                    again, thank you for your time.

                    • 7. Re: Weekly Sales
                      Stoyko Kostov

                      Hi Arthi,

                       

                      I'd like to understand the following: What would you like to do with the 2 values you need a formula for? Display them, or use them in further calculations?

                       

                      If the former, I think the solution I first proposed (using rank() function) serves the purpose. If the latter, we'll need to come up with a formula based on your end result.

                       

                      I don't think it's possible to store these values in intermediate parameters for example (so you can use them further as new requirements arise). The formula that I gave you would only give you the index value, but you can't use it to look up another column: the LOOKUP() function is a table calculation and works only on entire tables, not on particular rows.

                       

                      So, if you tell me your ultimate goal, I may be able to recommend something.

                       

                      Thanks!

                      • 8. Re: Weekly Sales
                        Stoyko Kostov

                        I was able to find a way to look up a column based on an index column in another thread: How to show the corresponded date of a certain value.

                         

                        I'm attaching a solution using this idea. It uses the formulas for computing the ultimate and penultimate quarters as I suggested above, but it needs to start from a presumed 'date of report'. If you know the date of the report will always be today's date, you can replace references to [Date of Report] with today().

                         

                        The lookups that I'm doing are the following:

                         

                        IF [Quarter] = [LatestQuarter]

                        AND [Week in Quarter] = (IF LEN([LatestWeekInQuarter]) < 7 THEN '0' ELSE '' END) + [LatestWeekInQuarter]

                        THEN [Sales] END

                         

                        IF [Quarter] = [LatestQuarter]

                        AND [Week in Quarter] = (IF LEN([PreLatestWeekInQuarter]) < 7 THEN '0' ELSE '' END) + [PreLatestWeekInQuarter]

                        THEN [Sales] END

                         

                        Then I dragged the calculated fields to the chart area on a new sheet.

                         

                        You can play with the DateOfReport parameter to see how your values change.

                         

                        This doesn't take into account the case where the 2 weeks you need reported are straddling a quarter boundary (e.g. Q2 week 13 and Q3 week 1). You will need some if-then cases in the definitions to cover this case.

                        • 9. Re: Weekly Sales
                          arthi.keat

                          works like a charm. you are a genius!! this solves what my original requirement is.

                          Please try to look at the below complexity to the same requirement if you have some time or I can create a separate thread.

                           

                          Now, that we got latest week sales and pre-latest week sales, I would like to show pre-latest week sales till Wednesday of every week and then on every Thursday I need to change it to latest week sales.

                           

                          any idea on how we can get this..

                           

                          thank you again for your time and patience in this regard. you have been very helpful.

                          • 10. Re: Weekly Sales
                            Stoyko Kostov

                            Thanks Arthi

                             

                            If I understand your question correctly, all you need to do is add a calculated field like this:

                             

                            IF (DATEPART('weekday', [DateOfReport]) <= 4) THEN [PenUltimateWeekSales] ELSE [UltimateWeekSales] END

                             

                            4 is for Wednesday (Sunday is 1, Monday - 2 etc).

                             

                            Again, if you need this to be based off of today's date, just replace DateOfReport with today().

                             

                            See attached.

                            1 of 1 people found this helpful
                            • 11. Re: Weekly Sales
                              arthi.keat

                              Stoyko Kostov

                               

                              Thank you so much. It is exactly what I needed. It works perfectly!!

                               

                              Thank you so very much for your time and patience.