Using Year function in set analysis

[nikita42]

Hi,

I have a field called [Current Hire Date] in my model which is a date. I have a list box on my UI which just shows the Hire Year using Year(Current Hire Date) formula.

I have data sources for Dec 2015 and every month so far in 2016. What I need is to build out a chart that shows the number of resources who joined in 2015 or 2016 AS PER the data in that year. In other words, if the user selects 2015 as the Hire Year, I need to display the count of resources who joined in 2015 as of Dec 2015 and not as of today (since some people may have left since 2015).

I'm trying to do something like:

sum({<Year=Getfieldselections(Year(Current Hire Date)Headcount), but this doesn't work.

I need to keep this calculation on the front end, so I can't add Year(Current Hire Date) as a dimension in my script.

Any sugestions?

[marcus_sommer · Accepted solution]

Yes there are some syntax rules but they are not so complicated like it looked at the beginning. Here you will find a very good explantion with many examples: Set Analysis: syntaxes, examples

For your expression, try this:

sum({<[Current Hire Date]={"

     >=$(=date(floor(YearStart(GetFieldSelections([Current Hire Date])))))

     <=$(=date(floor(YearEnd(GetFieldSelections([Current Hire Date])))))"}>}Headcount)

The reason are that you could get a yearstart from a year esle you will need a date and yearstart returned a timestamp which needs to be truncated per floor() and then it needs to be formatted again - then by set analysis must not only the values fit also the formatting must be the same.

- Marcus