3 Replies Latest reply on Jun 4, 2017 10:24 AM by Okechukwu Ossai

# Calculating number of orders in the previous week for a selected current datetime

Hello Team !

I have the scenario as follows :

1). The user can select a date from the DateParameter (DateTime data type). Lets say user chose 05/31/2017 at 9:00 PM. User wants to count the number of orders (data source attached ) from 05/31/2017 12:00 AM to 05/31/2017 1:00 PM (because this is the latest available datatime in the data source ).

2) Similarly , user wants to count the number of orders in the previous week for the same day and same time stamp. i.e from 05/24/2017 12:00 AM to 05/24/2017 1:00 PM

Appreciate any help on this. Thanks in advance !!!

Attached the sample data source and twbx.

Tableau version : 10.1.1

Regards,

Kalluri Siva

• ###### 1. Re: Calculating number of orders in the previous week for a selected current datetime

Hi Kalluri,

Is this what you are looking for? I noticed that the date formats changed when I downloaded the Tableau workbook. So, I had to reconnect the Excel data source. This may be due to Workbook Locale settings. So, remember to reconnect the Excel data source if you experience similar issues.

My understanding is that you want to count the number of orders between two date limits (lower and upper) controlled by a parameter. Below is my approach. I divided the solution into smaller chunks for better understanding. Feel free to combine them into a single formula if you wish.

1. Create calculated field [Lower Date Limit]

DATETRUNC('day', [DateParameter])

This truncates DateParameter to 12:00 AM of the selected date

2. Create calculated field [Upper Date Limit]

{FIXED:  MAX(IF [DateParameter] <= {FIXED DATE([Date]): MAX([Date])} THEN [DateParameter]

ELSE {FIXED DATE([Date]): MAX([Date])} END)}

This will return DateParameter if it is less than or equal to a datetime in the data source. Else, it should return the latest available datetime in the data source for the selected date.

3. Create calculated field [# of Orders Current Datetime]

{FIXED: SUM(IF [Date] >= [Lower Date Limit] AND [Date] <= [Upper Date Limit] THEN 1 ELSE 0 END)}

This formula counts the number of orders for selected datetime

4. Create calculated field [# of Orders Previous Week]

{FIXED: SUM(IF [Date] >= DATEADD('week', -1, [Lower Date Limit]) AND [Date] <= DATEADD('week', -1, [Upper Date Limit]) THEN 1 ELSE 0 END)}

This counts the number of orders for same period in the previous week.

Hope this helps.

Ossai

• ###### 2. Re: Calculating number of orders in the previous week for a selected current datetime

Thank You Okechukwu Ossai. This helped me . Appreciate your help.

• ###### 3. Re: Calculating number of orders in the previous week for a selected current datetime

You're welcome Kalluri. I'm glad it helped.