9 Replies Latest reply on Oct 23, 2018 5:57 AM by Rahul Singh

# Date Calculation

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

Hi Vandana,

Find attached my solution.

Regards,

Rahul

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

Hi Rahul,

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

Thanks,

Jitan

• ###### 3. Re: Date Calculation

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

Thanks Rahul!

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.

Vandana

• ###### 5. Re: Date Calculation

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

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?

Vandana

• ###### 7. Re: Date Calculation

Hi Vandana,

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
• ###### 8. Re: Date Calculation

Hi Rahul,

How are you?

If i check the dashboard today i.e. Monday I do not see the numbers for last Fri, Sat and Sun. Although the data is there until yesterday in our database.

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.

Thanks,

Vandana

• ###### 9. Re: Date Calculation

Hi Vandana,

I think the problem lies in what is the starting day of the week.

I have used the example of Monday as the starting day of week that's why the 3rd argumnet in "DATEDIFF('week',[Booking Date],Today(),'Monday')" is Monday. So, if today is Monday then last friday will fall in last week and not in current week. Assuming that your week starts from "Friday" then the calculation would look like this:

Current week Friday, Saturday and Sunday Bookings:

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

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

THEN [Amount] ELSE 0 END

Let me know if this helps.

Regards,

Rahul