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.
Thank you for your quick reply.
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.
1 of 1 people found this helpful
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.
Thanks again. Your answer was very helpful.
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.
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?
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"
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.