1 2 Previous Next 18 Replies Latest reply on Oct 4, 2016 11:34 AM by David Li

How to calculate weekdays dynamically and sum of those days of current week as well as last week

I want to compare the total sales of Sunday,Monday,Tuesday,Wednesday of last week with Sunday,Monday,Tuesday,Wednesday of current week.

i want to display the last week first 4 days total sum value and as well as current week 4 days total sum value. the values should change based on the date changes.

Ex: if am in thursday of current week means i need have a total of all the completed days wednesday,tuesday,monday,sunday of current week and relevant days in the last week also.

whenever the date changes accordingly i need to display the total sales dynamically in Tableau.

Let me know any questions.

Thanks & Regards,

Seenu

• 1. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi,

I recommend you have a look at this great blog post. It will answer your question and allow you to do way more:

Hope this helps!

• 2. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi Stephane,

Actually i want to do this using calculated fields, I want the results to change it dynamically. I don't want to use parameters or filters.

• 3. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi Anandh, what should the calculation do if it's earlier in the week, say Tuesday? Will it stop on Sunday or continue into the previous week?

• 4. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

You can either do a datediff('day',[Your Dimension],today())<15 to get a rolling 14 days or a datediff('week',[Your Dimension],today())<3 to get the last two weeks of data.  You can also set your start day of the week by having it as another option in that calculation.

Example:

datediff('week',[Your Dimension],today(),'sunday')<3  will give you the last 3 weeks starting on a Sunday.

I hope this helps.

Thanks,

Kristy

• 5. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi David Li,

Suppose if am on current week Tuesday i need to show total sum of weekday starting from sunday, monday and tuesday values of current week as well as i need to capture the previous week sunday, monday and tuesday values also. Likewise based on my day changes i need to calculate the sum value of all the previous days including current day of week, and relevant days from previous week also.

i have attached the excel sheet, every week my day is starting on sunday.

I hope i explained what i want exactly.

Please let me know if you have any doubts.

this is want i want

just assume if am on Tuesday today means i need to calculate the total sales  of sunday, monday, tuesday of current week, as well as total sales of sunday, monday, tuesday of previous week also. if am moving to wednesday i want to have sunday,monday,tuesday,wednesday of current week as well as sunday,monday,tuesday,wednesday of previous week also.

Thanks,

• 6. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi Kristy,

Here i don't want to use the rolling calcualtion.

Below is the results i want

this is want i want

just assume if am on Tuesday today means i need to calculate the total sales  of sunday, monday, tuesday of current week, as well as total sales of sunday, monday, tuesday of previous week also. if am moving to wednesday i want to have sunday,monday,tuesday,wednesday of current week as well as sunday,monday,tuesday,wednesday of previous week also.

• 7. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Try using something like this as a filter:

DATEPART('weekday', [Date]) < DATEPART('weekday', TODAY())

Filter in the values that are TRUE, and make sure to put week number on the row or columns shelf to break up the remaining days into different weeks.

• 8. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi David,

I am expecting  the below result

Just look the above image i am on week 53 wednesday so i want to calculate and display the total value of all the days of current week, and we need to compare the current day with previous week wednesday and before days total values also.

Suppose if am moving to thursday of week 53 then i want to display the total of current week from sunday to thursday , similarly i need to calculate for week 52 fro sunday to thursday as well.

here the calculation is a kind of  comparison between current week weekdays with previous week weekdays . whenever the day or date gets changed i want to display the total accordingly.

But i want all this things has to change dynamically based on the day/date changes.

I hope the above image gives you a clear picture that what results am expecting.

Thanks

• 9. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi Kristy,

I am expecting  the below result

Just look the above image i am on week 53 wednesday so i want to calculate and display the total value of all the days of current week, and we need to compare the current day with previous week wednesday and before days total values also.

Suppose if am moving to thursday of week 53 then i want to display the total of current week from sunday to thursday , similarly i need to calculate for week 52 fro sunday to thursday as well.

here the calculation is a kind of  comparison between current week weekdays with previous week weekdays . whenever the day or date gets changed i want to display the total accordingly.

But i want all this things has to change dynamically based on the day/date changes.

I hope the above image gives you a clear picture that what results am expecting.

Thanks

• 10. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi Anandh, the solution I suggested will give you this result. Since it's Monday today, you'll only get one day for each week. If you want to pick a different anchor date, you'll need to replace TODAY() with a date parameter.

See the output below and the packaged workbook (v9.3+).

• 11. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi David Li,

But business users want to compare the weekday wise sales which i mentioned above. They want the exact results which i mentioned above. Please help me if you have any other solution by using parameter atleast if not changing dynamically.

One more scenario i also want to display current week upto whichever the weekday is running upto that calculate total sales and last entire week total sales values.

Thanks

• 12. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Like this?

• 13. Re: How to calculate weekdays dynamically and sum of those days of current week as well as last week

Hi David,

somehow like this only, but whenever the day/date gets changed the values also should change accordingly it has to pick the current weekday dynamically. Please share the workbook as well so that i can have a look and let you know the exact results is coming or not.

Please share the workbook as well. Please make sure the weekdays has to change dynamically and produce the total values of that.

whatever you shown is similar, share the workbook i will check and confirm