Current week/month/quarter values in one table

[craigsmith]

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. We are on 4.2 SP9.

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”. But that does not work; even with 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 adding a separate query that returns one record with the latest week, month, and quarter dates. I had to create a measure variable with a max function to be able to use them in calculations, and the data is correct at the detail level but it won’t aggregate properly. I tried merging them but then it filters the entire table on the latest week.

I have tried creating various “sum where” and “sum if” variables for each column and they either simply do not work at all or give multivalue or other errors.

How can I accomplish this?

[ayman_salem]

Can you share sample data?

[craigsmith]

Thanks. I tried many versions of the in as well as the foreach and forall context operators and cannot get them to give what I need. They work if my rows are a time dimension, but my row dimension is departments in the company, and for the columns I only want the values for the current week, month, quarter, and year. And if I filter the whole table on the latest week it doesn't work.

However, I was able to get something to work. After so many iterations I am not 100% sure I have it straight though. This is complicated by the fact that I have to create many variables for each date context, which are percentages of different types of time charging compared to the total. I found I can do a sum where on a sum if variable, but not the other way around. And the sum if only worked for a real measure, not a variable. I thought it might be a context issue but tried to eliminate that, but who knows. At any rate, I got it to work, but it's requiring a lot of formulas.

[drs_macgyver]

I suggest working with the report's natural aggregation. Have your query pull the lowest level detail. You mentioned week or probably down to the day. Then on the columns in your report table, change the measure formulas (or I actually recommend creating variables), and add in ([dimension]) at the end. Where dimension is the object that contains the date dimension (week, quarter, year). You could have a column for measure in ([week]), one for measure in ([qtr]), and one for measure in ([year]).