Expressions de-coupled from certain dimensions in a pivot?

[richnorris]

Hi all,

I've been given a business requirement of a pivot showing VA, PC and Volume, month on month for the most recent two years. As well as showing this, the pivot has to show:

Previous Business Day
Current MTD Average
Current YTD Average
Prior Year Average

My first thought was to not include a date dimension, but make each of the columns a separate expression, one for each year / month.
unfortunately since the months and years have to be dynamic and update with selections, I cant really do this. My next thought was to have
a dimension of 'Year-Month' with all possible date values, then concatenate values for each of the four calculations. I would then do
a conditional expression of something like

if(Year-Month='Previous Business Day', Calculate previous business day's VA, sum(VA))

for each expression. The trouble with this is, the column 'Current MTD Average' would have to have all MTD VA values, but it wont have any?

Is this even possible?

Thanks in advance for any and all help and advice!

(Also, apologies for the double post / previous post being empty, had some IE issues.)

[#2]

I don't know if I exactly follow you, but you can force an expression to ignore dimensions using the TOTAL modifier. For example:

Sum(TOTAL Sales)

If that was your expression for Current MTD Average, it would calculate for all records in the dataset ignoring dimension. If you'd like to respect a dimension, but ignore others, use:

Sum(TOTAL <RespectThisDimension> Sales)

You can respect more dimensions by separating them with a comma: <Dim1, Dim2...>

[richnorris]

Ah, that sounds like its along the lines of what Im after! The problem is though, that I've basically got, for each month, 4 columns. VA, PC, Volume and TV. I have totals underneath these. However, after all of these, I want the other calculations, the averages. It sounds like I could probably get the right results with TOTAL, but I need the headings to be 'MTD Average' and not 'Jan - 10' or 'Unknown Month'.