12 Replies Latest reply on Sep 28, 2014 5:56 PM by Matt Lutton

# Calculated field for last day of a given week (not the weekly average)

Hi

I have a dataset of stock prices and I am interested in looking at how the price moves week to week. However, I don't want to look at the average, I just want to look at the most recent stock price for Friday versus last Friday's stock price. Is there a way to do this with a calculated field? Thank you.

• ###### 1. Re: Calculated field for last day of a given week (not the weekly average)

What does your data look like? Is there one row per day, or does each day have multiple rows with prices at different times through the day?

Can you post your data set, or a sample?

There could be many ways to tackle this, but much depends on your data structure.

• ###### 2. Re: Calculated field for last day of a given week (not the weekly average)

Hi,

I've posted a sample workbook. I basically want to create a filter so that I can view ONLY the last day of the week. Since this is daily financial data it would most likely be Friday, although if Friday was a business holiday it could be early.

Would love any feedback!

• ###### 3. Re: Calculated field for last day of a given week (not the weekly average)

You can use this formula and manipulate according to your requirement,

if datename('weekday',max([Date])) = 'Friday' then max([Date])

else if datename('weekday',max([Date])) = 'Monday' then date(max([Date]) - 3)

else if datename('weekday',max([Date])) = 'Tuesday' then date(max([Date]) - 4)

else if datename('weekday',max([Date])) = 'Wednesday' then date(max([Date]) - 5)

else if datename('weekday',max([Date])) = 'Thursday' then date(max([Date]) - 6)

else if datename('weekday',max([Date])) = 'Saturday' then date(max([Date]) - 1)

else if datename('weekday',max([Date])) = 'Sunday' then date(max([Date]) - 2)

end end end end end end end

1 of 1 people found this helpful
• ###### 4. Re: Calculated field for last day of a given week (not the weekly average)

Would this let me calculate the change from the end of one week to the end of the other? I can't get Tableau to understand that I do not want to average the week, I just want to look at the change from the end of one week to the end of another.

• ###### 6. Re: Calculated field for last day of a given week (not the weekly average)

Can you share a sample workbook with your work.

• ###### 7. Re: Calculated field for last day of a given week (not the weekly average)

Hi, Please find a sample attached. Thank you.

• ###### 8. Re: Calculated field for last day of a given week (not the weekly average)

I managed to arrive to a solution (I think) but it turned out to be quite complicated. Maybe others will find a simple approach.

The main problem is that your data has missing dates. If each week had all 7 days, then we can just pick only Fridays, apply trivial calculation to compare each Friday's value to the previous Friday, and Bob's your uncle.

But in your data some weeks only have data for 3 days, some 4, etc.. So to solve this I had to follow these steps:

1. Partition data into weeks
2. In each week find the last record, which could be Friday, or Thursday, or Wednesday, etc.
3. Get the value of that last record and push it into the next week, so it can be accessed from there (this sounds complicated because it is, see below)
4. Compare the value from step 3 (previous week) to the one for "current" week and calculate the difference.

The solution uses table calculations, which is a complicated subject worthy of a PHD. If you are not familiar with these, Tableau Help has some good introductory info. In a nutshell, normal calculations can only access values from the same row of data, but table calculations can access values from other rows.

In this case we need to grab all rows for a week and use table calculations to find which row is the last one.

If we had 7 rows in each week, then the next step would be to compare that last row to the one 7 rows back. But instead of constant 7 the number of rows varies from week to week, thus the need for step 3 above and a complicated solution.

1 of 1 people found this helpful
• ###### 9. Re: Calculated field for last day of a given week (not the weekly average)

Thanks Dimitri - this is great!!!! Amazing what can be learned on these forums!

• ###### 10. Re: Calculated field for last day of a given week (not the weekly average)

Just looking over this; great work Dimitri, this is really difficult stuff to set up...

Just a quick note/question--don't we currently see marks stacked one on top of the other in the first column for each Company?  When I open the solution, I'm seeing 1620 marks in your view;  I'm thinking the Is Last T/F filter with a Compute Using (Addressing) on Week number, then date, restarting every Week number (same as the other Table Calcs in the view) would reduce the number of marks and eliminate the bold-looking values in the first column for each Company.  They appear bold because its the same value stacked on top of itself.

I'm attaching the workbook with this filter applied--hopefully, I didn't screw anything up here.  Cheers!

• ###### 11. Re: Calculated field for last day of a given week (not the weekly average)

Thanks, Matthew, nice cosmetic improvement.

I usually insert IIF(LAST()==0,[Value],Null) into the calculated field itself to avoid stacking display (filter works a treat too), but I  didn't want to overcomplicate the already complicated solution.

• ###### 12. Re: Calculated field for last day of a given week (not the weekly average)

No worries, I am no expert. Really just following a lot of threads to pick up on what others do. The calc was already in the data window so I assumed someone meant to use it. I often build into the calcs as well. Cheers