Does no-one have any ideas re this? There must be some ideas on how to report mixed time dimensions on one table? There was an earlier question like this but I can no longer view the content.
John, can you post a sample workbook? Much depends on how you data is structured, especially if your data is already aggregated into weeks as seems to be the case from your description.
Please see attached. Essentially I just want to show the collections for the month (£131k) and the customer numbers for week 52 (1934). We want to automate this report if possible so I was thinking of either adding a relative date filter for month to date or the last week but not sure how to represent both.
Please note this test twbx is just linking to an Excel spreadsheet. We will be connecting to a proper database once we go live so will add a date table to make things easier.
Any suggestions on the best way to approach this?
mtd+ytd workbook.twbx.zip 39.5 KB
1 of 1 people found this helpful
I think the best way to approach this would be to use custom calculations. One would calculate collections for last month, i.e. MTD, while the other - customers in the last week. Then you won't need to apply any filters, the formulae will take care of it, so you can show both on the same sheet.
The problem is that your data doesn't really have a proper date dimension, it just has a text field with month names and a number field with week numbers. Any chance you can get proper dates from your data source? If not, then some other auxiliary calculations need to be done to make sure that your "dates" have the right sort order and hierarchy for this approach to work.
Additionally, what happens if the month ended 2 days into the week? That last week of the month will only have 2 days' worth of the last month and 5 days' worth of the next month.
Thanks Dimitri. We will create a date table with week numbers linking to fiscal dates and that should help. The month to date numbers would be based on fiscal weeks (4-4-5) rather than actual dates so your last scenario won't apply.
Now just have to figure out how to do the custom calculations for month to date and last week