
1. Re: Calculated field for last day of a given week (not the weekly average)
Dimitri.B Sep 23, 2014 7:30 PM (in response to luciana.suran)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)
luciana.suran Sep 24, 2014 10:26 AM (in response to Dimitri.B)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!

Book1.twbx 43.4 KB


3. Re: Calculated field for last day of a given week (not the weekly average)
preeti.prajapati Sep 24, 2014 12:47 PM (in response to luciana.suran)1 of 1 people found this helpfulYou 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

4. Re: Calculated field for last day of a given week (not the weekly average)
luciana.suran Sep 24, 2014 12:56 PM (in response to preeti.prajapati)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.

5. Re: Calculated field for last day of a given week (not the weekly average)
Matt Lutton Sep 24, 2014 12:57 PM (in response to luciana.suran)You might also find this thread useful:
Find Earliest/First/Least and/or Latest/Last/Maximum of a Field and Use that for Other Fields

6. Re: Calculated field for last day of a given week (not the weekly average)
preeti.prajapati Sep 25, 2014 6:27 AM (in response to luciana.suran)Can you share a sample workbook with your work.

7. Re: Calculated field for last day of a given week (not the weekly average)
luciana.suran Sep 25, 2014 6:58 AM (in response to preeti.prajapati)Hi, Please find a sample attached. Thank you.

Book3.twbx 49.0 KB


8. Re: Calculated field for last day of a given week (not the weekly average)
Dimitri.B Sep 25, 2014 7:54 PM (in response to luciana.suran)1 of 1 people found this helpfulI 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:
 Partition data into weeks
 In each week find the last record, which could be Friday, or Thursday, or Wednesday, etc.
 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)
 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.

9. Re: Calculated field for last day of a given week (not the weekly average)
luciana.suran Sep 26, 2014 7:43 AM (in response to Dimitri.B)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)
Matt Lutton Sep 26, 2014 8:39 AM (in response to Dimitri.B)Just looking over this; great work Dimitri, this is really difficult stuff to set up...
Just a quick note/questiondon'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 boldlooking 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 appliedhopefully, I didn't screw anything up here. Cheers!

11. Re: Calculated field for last day of a given week (not the weekly average)
Dimitri.B Sep 28, 2014 5:51 PM (in response to Matt Lutton)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)
Matt Lutton Sep 28, 2014 5:56 PM (in response to Dimitri.B)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