1 2 3 Previous Next 37 Replies Latest reply on Dec 24, 2013 9:28 AM by Jonathan Drummey

# How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

I do not want the monthly values to SUM, I simply need to use the last value provided for the last week of each month and use that value to create a monthly trend line.  A calculated field is preferred as this report will be automated and I can't make date selections from the filtered dimension.  Any help would be truly appreciated.

• ###### 1. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

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).

• ###### 2. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

Eric,

First, I created a calculated field called "Last Day" with the following formula:

STR(MAX(MONTH([Week Of]))) + "/" + STR(MAX(DAY([Week Of])))

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!

Best,

Dallin

1 of 1 people found this helpful
• ###### 3. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

I just realized you'll probably also want to have Year in the columns shelf if you're going to be plotting this across multiple years.

• ###### 4. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

Thanks, Dallin

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.

Any suggestions?

• ###### 5. Re: Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

With his solution, does changing the SUM(Value) to MIN(Value) provide the correct results:

Version 8.0 Workbook attached.

• ###### 6. Re: Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

Note that this view could be created by simply dragging MONTH(Week Of) to Columns and MIN(Value) to Rows (no last day needed).  I'm not sure if this would work for your production scenario:

• ###### 7. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

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.

• ###### 8. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

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.

• ###### 9. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

I'm going to guess that Joshua Milligan can come up with an ideal solution for this one, if he has the time to chime in. I continued to try and come up with a better solution but have failed.

• ###### 10. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

You are not alone Matthew.  You can see how long I have been working on it.  :-)

• ###### 11. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

With Dallin's setup, if we use Value as a dimension (not an aggregate), we start to see more than one date per month:

It just seems like there ought to be a way, from here, to filter for the max date within a month.

• ###### 12. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

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!

• ###### 13. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

Matthew,

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!

Regards,

Joshua

• ###### 14. Re: How do I take the last value for each month (data collected weekly) and use that value for monthly trend?

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.

1 2 3 Previous Next