Set Analysis issue...

[#1]

Hi,

I'm having some issues with set analysis in a chart I'm trying to create.

My fact data has a field SERVICEDATE. I am trying to create a chart that plots values for 2012, 2013 and 2014 alongside each other.

Using expressions such as: =sum({$<SERVICEDATE={'*/2012'}>}SALESPRICEEUR) is working fine. What I would like to do though is make these dynamic, so they show the previous year, the current year and the next year. That way, come 2014 I don't need to go through all my charts and change the expressions.

I created three script variables:

let vPriorYear  = year(today())-1;

let vCurrentYear  = year(today());

let vNextYear = year(today())+1;

I tried updating the expression to: =sum({$<YEAR(SERVICEDATE)={$(vPriorYear)}>}SALESPRICEEUR) and although the expression editor confirms this is OK, I get no results returned. I have verified the content of the variables by just displaying them in a text object, so I know the values are in there as expected.

Any suggestions?

Thanks,

Rory.

[swuehl · Accepted solution]

You can't use an expression like year(ServiceDate) on the left side of a field modifier in a set expression, only field names are allowed.

So best would be to create a year field in your data model using

LOAD

     SERVICEDATE,

     Year(SERVICEDATE) as SERVICEYEAR,

....

(or create a master calender), and then use this field in your set expression:

=sum({<SERVICEYEAR = {$(vPriorYear)}>} SALESPRICEEUR)