Set analysis with dates - how to include a year() on the actual test date

[#1]

Hi Everyone,

I'm sure this is an easy question for someone to answer, but I'm pretty new and can't seem to find the answer.

I have a date R_SALE_START_DATE and I want to evaluate whether the YEAR of this date is >= a variable called $(TenYear).

So set analysis, I need to tell the following statement not to evaluate R_SALE_START_DATE which is a full date, but to evaluate =year(R_SALE_START_DATE):

=sum( {<R_SALE_START_DATE={"<=$(TenYear)"} variable1)

What is the correct syntax to do this?

Thanks

GPC

[fosuzuki]

Hi RPC,

I think that in the set analysis you cannot use expressions like year(R_SALE_START_DATE) to define the field you want to modify (for example =sum( {<year(R_SALE_START_DATE) ={"<=$(TenYear)"} variable1).

So if I am correct, you could create a field in your load script (I'd recommend to create it in a Time dimension):

year(R_SALE_START_DATE) as Sale_Start_Year

and use it in the set analysis:

=sum( {<Sale_Start_Year={"<=$(TenYear)"} variable1)

Hope this solves your problem.

Regards,

Fernando

[rwunderlich]

You could invert the problem and make TenYear a date value in the test like this:

=sum( {<R_SALE_START_DATE={"<=$(=makedate($(TenYear),12,31))"}>} variable1)