Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)