Anyone out there?
I need to select the latest date for each of the last 13 months so those 13 dates can be used to trend. Someone much smarter than me could perhaps assist with calculated field(s).
1 of 1 people found this helpful
First, I created a calculated field called "Last Day" with the following formula:
Then, I dragged it onto the Columns shelf.
Next, I dragged the "Week Of" field onto the Detail shelf and set it to Month.
Last, I changed the mark type to line.
Here is the result:
I hope that's what you were looking for!
This is close, but the Values still SUM for the wkly values collected for each month:
3/31 should be 9 instead of 29
4/28 should be 8 instead of 35
5/26 should be 7 instead of 31
and so forth...like shown on the "Desired Outcome" tab.
Thanks for jumping in, Matthew. Your formula works, but only because the MIN value happens to also be the last value for each month's weekly data collection. There has to be a way to do this. In sudo code, it would look like this:
VALUE for MAX DATE of each month in [Week Of]. I will continue to scratch my empty head.
I understand the reason it works, but if that was always the case (as it is in this data), then there's no reason not to use this method. I assume you have other data that isn't in this data set. It can definitely be done, that much I know. Its just a matter of getting there.
You are not alone Matthew. You can see how long I have been working on it. :-)
Actually, taking what Joshua showed me in another workbook, I took what I had above, and simply created a calculation called LAST() with a definition of: LAST()
Set that to discrete (blue pill), placed it on the filters shelf and set the Compute Using >> Advanced to be on MONTH(Week Of), then keep only the values of 1.
Let me know if this works for you!
DateAssist ML 8.0.twbx 24.2 KB
Thanks for the ping! You are right about time being a huge factor recently -- I'm hoping things will calm down soon and I'll return to regular contributions on the forums. Historically this time of year has been slow, but this year things keep picking up speed! That's not a bad thing...
After a quick read through of the thread, I think you are on to a good solution in the last post. The Last() function is almost certainly going to be a good way to get the answer. If that's still not quite it, I'll be happy to come back and take a look!
Very close, but the values are still wonky.
March value (9) is missing.
April should be 8
May should be 7
July value (5) is missing
August should be 4
September should be 3
I can't tell you how many calculated fields I have tried and failed. Matthew, this is the closest to closing the deal.