7 Replies Latest reply on Sep 26, 2018 7:01 PM by Rahul Singh

    Date Calculation

    Vandana Samtani

      Hi guys,

       

      I am working on a dataset and would need suggestions on how to  create calculated field for some metrics.

       

      The user is requesting to just get a roll up summary by release name - The dashboard should include Friday, Saturday, Sunday amounts from the first day or show release.

       

      Release Name      Friday Amount     Saturday Amount     Sunday Amount     Current FSS     Prior FSS     difference Current Week 1     Prior Week 1 difference

      Release 1                         $20                    $30                         $50                         $80               $100               -20%          $500                   $1000              50%          

      Release 2

      Release 3

      Release 4                          $20                    $30                         $50                         $80               $100               -20%          $500                   $1000              50%

       

       

      Current FSS -  Friday+ Saturday+Sunday (current week)

       

      Prior FSS- Friday + Saturday + Sunday (previous Week)

       

      Comparision of Current FSS Vs. Previous FSS.

       

      Another calculation -

       

           current 7 day amount = Week 1 (from release date)

           prior 7 day amount = Week 2

       

      and then comparison from Week 2  vs. week 1

       

      Any suggestions would be very helpful.

       

      Thanks,

      Vandana

        • 1. Re: Date Calculation
          Rahul Singh

          Hi Vandana,

           

          Find attached my solution.

           

          Regards,

          Rahul

          1 of 1 people found this helpful
          • 2. Re: Date Calculation
            Vandana Samtani

            Hi Rahul,

             

            I cannot open the workbook. Can you please re-publish?

             

            Thanks,

            Jitan

            • 3. Re: Date Calculation
              Rahul Singh

              Hi,

               

              Attached file in version 10.4.

               

              I have used a date from previous week as the reference date for whole calculation as there was no date from this week.

               

              Regards,

              Rahul

              1 of 1 people found this helpful
              • 4. Re: Date Calculation
                Vandana Samtani

                Thanks Rahul!

                 

                Very helpful.

                 

                Couple of questions -

                1)  in the logic below why are you adding the 2nd condition to determine if it's a Friday. I am learning so curious why that 2nd condition is needed.

                 

                IF DATENAME('weekday',[Booking Date])="Friday"

                AND DATEDIFF('week',[Booking Date],#2018-09-16#,'Monday')=0

                THEN [Amount] ELSE 0 END

                 

                2) Also, how would i know if it's week 1 (Friday - Sunday). Let's say the user wants only week 1 (Friday  - Sunday) or Week 2 after the release  how can they identify.

                Is there an possibility of week no to be added  by release name and release date so that the users can select  week no.

                 

                3)Same would be needed for Current Week and Previous week.

                 

                Thanks again for your help,

                Vandana

                • 5. Re: Date Calculation
                  Rahul Singh

                  Hi Vandana,

                   

                  1) The first line ensures we are getting amount for Friday only and 2nd condition ensures that we are getting only that friday which falls in current week. I have taken a ref date here which is 16 Sept 2018. You can use Today() instead of ref date to get the bookings made in current week's friday.

                  IF DATENAME('weekday',[Booking Date])="Friday"

                  AND DATEDIFF('week',[Booking Date],#2018-09-16#,'Monday')=0

                  THEN [Amount] ELSE 0 END

                   

                  2) To get week1 bookings (Friday-Sunday) from release date:

                      Week1 Friday Bookings:

                  IF DATENAME('weekday',[Booking Date])="Friday"

                  AND DATEDIFF('day',[Release Date],[Booking Date],'Monday')<7

                  THEN [Amount] ELSE 0 END

                  The 1st clause selects only friday and second clause selects only those fridays whch falls in the week same as release date. Similarly, Saturday or Sunday bookings in week 1 or week 2 can be calculated. To get combined Friday to Sunday bookings for a particular week, then add all those fileds (Friday to Sunday).

                   

                  3) To get the current and previous weeks bookings, which i have already shown in the file i shared. The logic is same as point 1, where find the Friday, Saturday and Sunday bookings (instaed of uisng any ref date use Today() function) and adding them will give current week bookings. Similarly to get previous week's booking, modify the calculation of current week where week difference will be 1 instaed of 0.

                   

                  Hope this helps.

                   

                  Regards,

                  Rahul

                  1 of 1 people found this helpful
                  • 6. Re: Date Calculation
                    Vandana Samtani

                    Thanks a lot Rahul! Very helpful.

                     

                    I was able to get the correct numbers for week 1 Friday, Saturday and Sunday.

                     

                    Couple of more question -

                     

                    1)  For Week 2 Friday Saturday and Sunday after release  I will have to change the logic here to >7 and  < 14. Right? How will this work?

                    Week2  Friday Bookings:

                    IF DATENAME('weekday',[Booking Date])="Friday"

                    AND DATEDIFF('day',[Release Date],[Booking Date],'Monday')<7

                    THEN [Amount] ELSE 0 END

                     

                    2) Just confirming -  For Current Week Friday Sat Sunday the  logic would be

                     

                    Current  Friday Bookings:  The release might have happen sometime in August and it's still running.

                    IF DATENAME('weekday',[Booking Date])="Friday"

                    AND DATEDIFF('day',[Booking Date], Today()'Monday')=0

                    THEN [Amount] ELSE 0 END

                     

                    Previous Friday Booking

                    IF DATENAME('weekday',[Booking Date])="Friday"

                    AND DATEDIFF('day',[Booking Date], Today()'Monday')=1

                    THEN [Amount] ELSE 0 END

                     

                     

                    2) Also, for the complete week 1 from release date (i.e.Friday to Thursday) How can i calculate this metric?

                     

                    Thanks again for your help,

                    Vandana

                    • 7. Re: Date Calculation
                      Rahul Singh

                      Hi Vandana,

                       

                      Glad that the solution helped you. For your queries:

                       

                      1) Calculation of 2nd Week Friday, Saturday and Sunday bookings:

                      IF DATENAME('weekday',[Booking Date])="Friday"

                      AND DATEDIFF('day',[Release Date],[Booking Date],'Monday')>=7

                      AND DATEDIFF('day',[Release Date],[Booking Date],'Monday')<14

                      THEN [Amount] ELSE 0 END

                      I have edited one portion in your formula, it should be >= 7 and <14, since next Friday will be 7 and not 8 (if release date falls on Friday) so, we have to include 7 as well in the calculation.

                       

                      2) Current week Friday, Saturday and Sunday Bookings:

                      IF DATENAME('weekday',[Booking Date])="Friday"

                      AND DATEDIFF('week',[Booking Date],Today(),'Monday')=0

                      THEN [Amount] ELSE 0 END

                       

                      In your formula, you have used 'day' in the 1st argument for DATEDIFF calculation it should be 'week' instead so that booking date and Today, both fall in the same week. Similarly for other days, replace friday by Saturday or Sunday.

                       

                      For previous week Friday bookings:

                       

                      IF DATENAME('weekday',[Booking Date])="Friday"

                      AND DATEDIFF('week',[Booking Date],Today(),'Monday')=1

                      THEN [Amount] ELSE 0 END

                       

                      The second clause ensures that there is a difference of 1 week in booking date and Today.

                       

                      3) Complete week 1 booking (Monday to Sunday)

                      IF DATEDIFF('week',[Release Date],[Booking Date],'Monday')=0

                      THEN [Amount] ELSE 0 END

                       

                      Throughout the calculation, i have assumed that your week starts from Monday. If that is not the case, let's your weekday starts from Friday (Week Cycles: Friday-Thursday) then just change the 4th argument in DATEDIFF calculation by "Friday" everywhere in all the calculated fields.

                       

                      IF weekday starts from Friday, then week 1 bookings calculation would be:

                       

                      IF DATEDIFF('week',[Release Date],[Booking Date],'Friday')=0

                      THEN [Amount] ELSE 0 END

                       

                      Regards,

                      Rahul

                      1 of 1 people found this helpful