I need to create a report with the totals for the current week, month, quarter, and YTD in one table. This type of report has always been problematic but I have succeeded on occasion using various methods.
I know I can do this by creating separate queries for each aggregate and merging them, but there are a lot of calculations that have to be duplicated and it runs really slow (x4).
I have tried to do it with one query for YTD and then adding latest week, month, and quarter flags using “if date = max(date) in report then 1 else 0”. But that does not work; even though I have the “in report” context it only returns the max date of the data in the particular table, which may be filtered.
I’ve also tried creating a separate query that returns just one record with the latest week, month, etc dates and latest flags; I had to create a measure variable with a max function to be able to use them in calculations. The data is correct at the detail level but it won’t aggregate properly.
I have tried creating various “sum where” and “sum if” variables and they either simply do not work at all or give multivalue or other errors.
So right now I am putting 4 separate tables next to each other so it looks like one, with separate filters on each, but there isn’t data for all dimensions in all weeks so they don’t line up.
I would think there has to be a way to do this but I think I’ve tried everything. Any ideas?