13 Replies Latest reply on Apr 12, 2016 8:37 AM by Corey Turner

# Determining this week

We have a field in Tableau which is our Financial week which comes in "hard coded" from our source data.  I would like to create a calculated field to determine "This week" so I could also then determine next week and last week as this +/- 1 for comparative analysis on a dashboard.

I have a screen shot of the worksheet below and we are in week 40 today (Wednesday so only sales up to and including Tuesday).  How can I determine that the current week is 40.  If I had a current week calculated field I could then show comparatives as I could put my current week calculated field in my filter shelf.  may seem an odd question but any help?

Ed

• ###### 1. Re: Determining this week

Ed,

You can write a "Current Week Number" calculated field as:

DATEPART('week', NOW())

If your current week isn't actually the current week of "today" and you want the maximum date/week of your data set you could try something like this:

Table Calc way: IIF(ATTR([Date]) = WINDOW_MAX(MAX([Date])), 'Current Date', 'Prior Date')

LOD way: IFF([Date] = {FIXED : MAX([Date])}, 'Current Date', 'Prior Date')

You may need to change the "Compute Using" of your table calc depending on your level of detail in your viz. Hope it helps.

1 of 1 people found this helpful
• ###### 2. Re: Determining this week

Corey,

Thanks for your response, those calcs are interesting/useful but don't really do what I want them to do.  I wanted something that would return the actual week number of the current week as the answer.

As per my attachment we are currently in week 40 so I'd like to get the result to tell me this so that I could then do some further logic on this.

Any thoughts?

Ed

• ###### 3. Re: Determining this week

Ed,

Sorry that didn't get what you were looking for, but now I'm a little confused as to exactly what you need. You said you want something that returns the actual week number of the current week....  DATEPART('week', NOW()) does this. For April 7, 2016, we get week 15.

I mentioned if the current week if dependent on your data (the hard coded values from your database) you would then have to do things a little differently with a table calc or an LOD.

Let me try again... you can try this table calc (#1) or LOD (#2):

1. WINDOW_MAX(MAX([Transaction Financial Week]))

2. {FIXED : MAX([Transaction Financial Week])}

These will return the maximum week number in your data set (being 40 from your screeenshot). If this still doesn't solve it, maybe upload an example twbx and describe what you are looking for in more detail. Hope this get it done for you!

Corey

• ###### 4. Re: Determining this week

Corey

Thanks for your patience it is appreciated but I must be doing something wrong as not getting it to work.

Attached is a revised screen shot.  I am working with the Financial Transaction Week field which as you can see underlined in red is not a date field.  Transaction date is.  I won't bore/confuse you with why we did this.  My current report shows the current weeks sales and prior 2 on a relative filter.

Ideally what I want to do is have the current weeks sales, next weeks sales and last weeks sales but shown by current year and prior years.  To do this i need to know what the current week is to then create 3 calculated fields one for current week, then one for next week and one for last week.  These 3 calculated fields could then be plotted against the years.

As you can see the financial week and native tableau week for the current year are the same so I could use the transaction date to return me the figure 40 for the current week (in theory) and then use this in my calculated field.

Using the Datepart ('Week',Now()) gave me an odd result as shown in my second screen shot?

The Window_Max also was odd as it gave me the answer 9 not 40 so very confused altogether now.

I'd try and send some data but not sue how to do that plus is a big big data set.

Ed

• ###### 5. Re: Determining this week

Ed,

Thanks for more details. This is tricky without playing with a workbook, but here are some thoughts that should get you a lot closer....

Your second screen shot you are summing this field... That will sum 15 over all the rows of your data to equal 147,432,810. If you just change this to a dimension, make it discrete (blue) you should get week 15.

Your third screen shot is looking for the maximum week as a string or text field. If you change the data type of this field you should be set. Right click the Transaction Financial Week and "Change Data Type" to Number (whole).  OR you can convert it in the calculated field if you still want the field to remain as text. WINDOW_MAX(MAX(INT([Transaction Financial Week]))) This should now return 40.

If you are able to post a twbx, I could help you get the views you described comparing the next/prev week for multiple years.

• ###### 6. Re: Determining this week

Corey,

So some progress as I got the answer of 40 but the data doesn't behave how I expected it to with it.  I have managed to attach an extract of my data.

If you look at "This Week Sales vs Last 2 Weeks" the sales for week 40 are 28,879 which is the current week.

Sheet 21 and 22 are the different versions of the calculation.

When I have determined the week my plan was to create a calculated field to filter on.

If it knew the current week was 40 then I could get it to tell me last week was 39 and next week 41.  I could then generate a table as below

2015/2016                         2015/2014          2014/2013

Last Week               xxxxx                                   xxxxx               xxxxxx

Current Week          28,879                                   xxxxx               xxxxxx

Next Week               (blank as no data yet)          xxxxxx               xxxxxxxx

Any thoughts?

Ed

• ###### 7. Re: Determining this week

Edward,

Check out the attached twbx, sheet 24.

I was able to get with a nested LOD calculation. You could do this with table calcs too, but I'd recommend LOD in this case.

IF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])} THEN

'Current Week'

ELSEIF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])}-1 THEN

'Last Week'

ELSEIF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])}+1 THEN

'Next Week'

END

Cheers!

1 of 1 people found this helpful
• ###### 8. Re: Determining this week

Thanks will look at that.  I'm on 9.2.4 you have done in a newer version?

Ed

• ###### 9. Re: Determining this week

Yes, I did it in 9.3 sorry about that. Are you able to update your version of Desktop?

1 of 1 people found this helpful
• ###### 10. Re: Determining this week

Yes but need to upgrade our server first so not a 5 min job.  When done all that I'll have a look at the workbook thanks.

Ed

1 of 1 people found this helpful
• ###### 11. Re: Determining this week

You can have multiple versions of Tableau installed on a client computer. Shouldn't be a need to install server first.

• ###### 12. Re: Determining this week

Corey

Now installed and seen what you did, really really helpful and much appreciated thanks for your help.  Now to understand the fixed formula..........

Ed