7 Replies Latest reply on Dec 18, 2013 7:03 AM by Eric Munisteri

# Value of the latest week in a month

Hi all,

I'm fairly new to Tableau.

Hopefully you can help me out with this, probably simple, question.

For one of my reports I am importing data on a weekly basis, but I want to report on a monthly basis.

Unfortunately, one of the values is a cumulative number. Meaning: the value of week 44 = the value of week 43 + the results in week 44. When I use this value in a report with MONTH(Date) in the column, I get the SUM of all the weeks in that month.

Is there any way to report the value of the latest week date a month, without summing all the valuea within the month?

Thomas

• ###### 1. Re: Value of the latest week in a month

Hi Thomas:

There is almost always a way!  If you can post a packaged workbook (.twbx file), someone will take a look at how to best approach your problem.   It is difficult to answer your question without a workbook to demonstrate with.  You can mock one up using the sample data provided if your data is too sensitive to post here.

• ###### 2. Re: Re: Value of the latest week in a month

Hi Matthew,

Please find a workbook attached with mock data in a simplified report.

As you can see in the workbook it shows the values of 'Participants' per 'Label', per week for the last month.

But I would like to report the value of 'Participants', per 'Label', per month for the last month - with the 'month' value being the value of the last week of the month (week 44 in de workbook).

Hopefully this clarifies my question.

• ###### 3. Re: Re: Re: Value of the latest week in a month

I do understand.  A simple filter applied to this view can limit the data shown to the last week.  The calculation is simply:

LAST()==0, placed on the filters shelf.  In this view, it defaults to Table>>Across which works for this view.  This will display only the last week in the view.

However, I'm not sure if that will help you get to your end solution.  I'm not sure how you want to display this information, or what options you plan to give to users, etc.  If this doesn't help you get what you need, please let us know what you would like to see as an end result.

1 of 1 people found this helpful
• ###### 4. Re: Re: Re: Re: Value of the latest week in a month

Hi Matthew,

Unfortunately, this pops up a new level to my question,

Your answer gives me a filter on the data, but I'm actually looking for a selection within the data.

I would like to use DATE(Month) in my column to show me the results of several months. But the value of each month should be the value of the latest week of that month. The combination of DATE(Month) in my column and your LAST() as a filter will only give me the results of the latest month.

I've updated the workbook with some more mock data to illustrate my point.

Hopefully you can help me out once again.

• ###### 5. Re: Re: Re: Re: Re: Value of the latest week in a month

I started to go about writing a calculation to split out the months based on the latest week # in that month, but I don't think that is necessary.  If I create a Filter on MONTH(Date), with the LAST()==0 filter in the view, it will only return the last week that fits within that month.  I created a "Month" label to simply put the label of "Month" above the column with sum(Participants) showing.

Using this setup, with a multi-select filter will result in only the most recent month's value being displayed.  If you need to display each month separately, then a new approach would be needed.

Will this work for your needs?

• ###### 6. Re: Value of the latest week in a month

Here is an example built by creating a new month field based on the maximum week # within each month (you'd have to verify across all months in your actual data set)--that calc looks something like this (for this sample data set):

IF DATEPART('week',(Date)) = 44 then "October"

ELSEIF DATEPART('week',(Date)) = 48 then "November"

ELSEIF DATEPART('week',(Date)) = 53 then "December"

end

Using this as the month field will result in what you want, I believe--but there is probably a better approach that someone else may recommend.