Calculated Dimension Error

[#1]

Hi, I'm really struggling with something that seems like it should be easy, hope one of you clever people out there can help...

I'm trying to create a calculated dimension, which works perfectly if I put it in a text box and the dimensions work perfectly on their own.

=if(month(max(IMPORT.SOLD_MMMYY)) - month(TODAY()) = -1, SOLD.YOYPeriod, SOLD.YOYPeriod2Mnths)

I can't figure out what I'm doing wrong, please help, thanks

[ChiragPradhan]

Seems like an issue with date formats. Check both dates are correct format.

[sunny_talwar]

I think since you are using Max() function here, you might need an aggregate function here. Won't really say much before knowing what you are trying to do. Can you elaborate what you are trying to do with your dimension?

[#4]

Hi all dates are the same format, what's confusing me is that if I copy the expression above into a text box and select certain dates from the calendars, the if statement works correctly and returns me the correct value.

[#5]

Hi Sunny, what I'm trying to do is select which dimension to use, this is determined as:

     if IMPORT.SOLD_MMMYY is one month behind the current month then use SOLD.YOYPeriod as the dimension,

        otherwise

     if IMPORT.SOLD_MMMYY is two months behind the current month then use SOLD.YOYPeriod2Mnths as the dimension.

Thanks, Simon

[#6]

sorry, if the latest IMPORT.SOLD_MMMYY

[ChiragPradhan]

Find attached a sample. Just bear in mind, its best to avoid calculated dimensions for large data.

[ramoncova06]

I agree with Sunny since you are using an aggregation in your calculated dimension, you need to use the function aggr

=if(month(aggr(max(IMPORT.SOLD_MMMYY),IMPORT.SOLD_MMMYY)), - month(TODAY()) = -1, SOLD.YOYPeriod,SOLD.YOYPeriod2Mnths)

[joe_nguyen]

Simon, I am late to this discussion, but I would add my solution just in case.

One of the most important rules for Calculated Dimension is that you can not used aggregation functions (sum, max, min etc). This is why your expressions worked perfectly in text boxes, but failed in Calculated dimension. To get around that limitation, you have to put all aggregation functions inside an aggr() one.

I believe your expression would work if written as follow:

=if(month(aggr(nodistinct max(IMPORT.SOLD_MMMYY), Year)) - month(TODAY()) = -1, SOLD.YOYPeriod,SOLD.YOYPeriod2Mnths)