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?

[sinanozdemir]

Edit: I was missing a parenthesis in the Year() function.

Hi Nikita,

You can use something like this:

Sum({<[Current Hire Date] = {">=$(=MakeDate(Year(GetFieldSelections([Current Hire Date])), 12, 31))"}>} Headcount)

I didn't test this so you may need to tweak it a bit.

Thanks

[nikita42]

Hi,

I don't need to use the MakeDate function. I need something more like - GetFieldSelections(Year(Current HIre Date)).

That's the bit that's throwing up errors.

[Anonymous]

Hi Nikita,

Use year before GetFieldSelections.

Year(GetFieldSelections(Current HIre Date))

Thanks!

[sasiparupudi1]

=sum({<Year={'=Year(GetFieldSelections([Current Hire Date]))'}>}HeadCount)

[marcus_sommer]

Try this:

sum({<Year = {"$(=Year(Getfieldselections([Current Hire Date])))"}>} Headcount)

and it work only for one selected date - otherwise you need a different approach to getfieldselections - maybe a max(date).

- Marcus

[nikita42]

Is there a way to use the Year function in this way:

{<Year(Current Hire date)=GetFieldSelection(Year)>}?

[marcus_sommer]

No, this isn't possible - the left part of the set analysis condition must be a field. But you could easily create such period-field within the script, see also: How to use - Master-Calendar and Date-Values.

- Marcus

[sasiparupudi1]

Script

B:

load

Date(Date#([Current Hire Date],'MMM YYYY')) as [Current Hire Date],

Headcount

Inline

[

Current Hire Date,Headcount

Sep 2015,40

Nov 2015,10

Dec 2015,30

Jan 2016,20

Feb 2016,6

Mar 2016,4

Apr 2016,22

May 2016,44

];

Filter : list box  with the expression

=year([Current Hire Date])

Text Box With the following expression

=count({<[Current Hire Date]={'=Year(Min([Current Hire Date]))=Year(Max([Current Hire Date]))'}>}Headcount)

hth

Sasi

[nikita42]

I was afraid of that! In that case, I'll need this expression to look something like this:

sum({<[Current Hire Date]={>=YearStart(GetFieldSelections(Year)<=YearEnd(GetFieldSelections(Year))}>}Headcount)

It'd be great if you could also explain a bit on the use of brackets and quotation marks in this expression, because that's where I keep going wrong.

Thanks!