Error in calculation?

[richnorris]

Hi, can anyone see what I'm doing wrong here? I have a calculated dimension of:

=if(Week < Week(Max([Trade Date]) ), Week)

Which should show all weeks other than the week of the maximum trade date, right? This isn't what I'm trying to achieve overall, so I'm not looking for alternatives, just why this peice of code isn't working?

[MK_QSL]

Instead of Calculated Dimension

Use

Dimension = Week

Expression

Something Like

SUM({<Week = {'$(=Week(Max([Trade Date])))'}>}Sales)

[richnorris]

So you're saying its impossible to fix that code to work?

[MK_QSL]

not like that...

this is alternate suggestion.

if you upload you sample file or apps, we can see what exactly wrong with your calculated dimension.

[swuehl]

I think you would need to use advanced aggregation (aggr() function) in your calculated dimension if you want to use an aggregation function.

But it's probably easier if you define

=max([Trade Date])

in a variable vMaxTradeDate, then use this in the calculated dimension:

=if(Week < Week(vMaxTradeDate), Week)

(and check if [Trade Date] is a date with a numeric representation, so max() will return a value)

[rwunderlich]

I think you just need to add the TOTAL qualifier.

=if(Week < Week(Max(TOTAL [Trade Date]) ), Week)

-Rob