Set Analysis issue...

Thu, 19 Dec 2013 11:44:10 GMT

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.

Re: Set Analysis issue...

swuehl — Thu, 19 Dec 2013 14:11:14 GMT

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)

topic Set Analysis issue... in QlikView

Set Analysis issue...

Re: Set Analysis issue...