Thank you for your quick reply. I tried what you have suggested, however, I am missing a column name?
I am just after a simple formula stating all sales to show within a specified range of days being Monday through to Sunday.
If you make you measure discrete and move it from the text shelf to the row shelf you'll get a column name:
What about the above table doesn't meet your requirement? If you take the table calculation off you'll get this:
That's just a count of records for each day of the week. Then you can use a date filter on the Filter Shelf to limit it to a specific month. If you can use the workbook I posted to mock up what you're looking for I'll be happy to help.
My apologies for not replying sooner.
Below is the custom formula that was pre-built which is not showing the right data.
IF DATEDIFF('week', [Deposit Taken Date] , Today()) - 1 = 0 THEN 1
ELSEIF DATEDIFF('week', [Settlement Date] , Today()) - 1 = 0 THEN 1
ELSEIF DATEDIFF('week', [Exch Date] , Today()) - 1 = 0 THEN 1
I need the formula to express - Monday, Tuesday, Wed, Thurs, Fri, Sat, Sun last week as these are the days our sales offices are open, therefore i need to show all sales made on these days.
Shawn, can you assist? I'm struggling here being new to all this..
So this is a workbook you inherited? See if this is what you're looking for:
IF DATEDIFF('weekday', [Deposit Taken Date] , DATETRUNC('week', Today())) - 6 = 0 THEN 1
ELSEIF DATEDIFF('weekday', [Settlement Date] , DATETRUNC('week', Today())) - 6 = 0 THEN 1
ELSEIF DATEDIFF('weekday', [Exch Date] , DATETRUNC('week', Today())) - 6 = 0 THEN 1
Before the formula was looking back 1 week. Now the formula first truncates Today to the first day of the week, which in Tableau world is always Sunday, and then looks back 6 days to the Monday, because that's what you said you wanted to be the first day of the week. Right? You'll need the Weekday on the column or row shelf to get all this to work. (See attached.)
Also this is just giving you a count of records, not a sum of sales. You could replace the 1 with SUM([Sales]) to get a total of sales on each weekday last week.
Let me know if you have other questions.
weekdays.twbx.zip 929.1 KB
Thank you for your reply.
I copied your formula, however, this gave me no result, leaving the columns blank.
This has got me baffled..
I do really appreciate your assistance though.
Did you look at the workbook I posted?