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

Anandh V Oct 3, 2016 2:32 AM

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.

Please help me to achieve this.@

Thanks & Regards,

Seenu

Test.xlsx 17.2 KB

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

Stephane Marx Oct 3, 2016 3:11 AM (in response to Anandh V)

Hi,

I recommend you have a look at this great blog post. It will answer your question and allow you to do way more:
https://interworks.co.uk/blog/date-comparisons-tableau-made-easy/

Hope this helps!
Like Show 1 Likes(1)

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

Anandh V Oct 3, 2016 3:45 AM (in response to Stephane Marx)

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.

Please help me on this and thanks for your reply.
Like Show 0 Likes(0)

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

David Li Oct 3, 2016 8:57 AM (in response to Anandh V)

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?
Like Show 0 Likes(0)

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

Kristy McGee Oct 3, 2016 9:35 AM (in response to Anandh V)

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
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 9:45 AM (in response to David Li)

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,
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 9:47 AM (in response to Kristy McGee)

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.
Like Show 0 Likes(0)

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

David Li Oct 3, 2016 9:51 AM (in response to Anandh V)

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.
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 10:12 AM (in response to David Li)

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 for your valuable reply.

Please help me to resolve this .

Thanks
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 11:10 AM (in response to Anandh V)

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 for your valuable reply.

Please help me to resolve this .

Thanks
Like Show 0 Likes(0)

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

David Li Oct 3, 2016 10:21 AM (in response to Anandh V)
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+).

Dynamic weekdays.twbx 1.2 MB
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 10:31 AM (in response to David Li)

Hi David Li,

First of all thanks for your reply. Actually the attached result i already achieved.
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
Like Show 0 Likes(0)

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

David Li Oct 3, 2016 11:11 AM (in response to Anandh V)

Like this?
Like Show 0 Likes(0)

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

Anandh V Oct 3, 2016 12:09 PM (in response to David Li)

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

Thanks for your effort.
Like Show 0 Likes(0)

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

David Li Oct 3, 2016 12:21 PM (in response to Anandh V)
When you mean that it "has to pick the current weekday dynamically", do you mean that it should set the weekday filter based on what day it is today? If so, my original solution will work. In any case, here's a new packaged workbook with that new formatting.

If you need the weekday to changed based on a parameter, used the other filter ("Weekday Today Filter").

Dynamic weekdays.twbx 1.2 MB
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post

Tableau Community

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Actions

More Like This