topic Re: Using Year function in set analysis in QlikView

Using Year function in set analysis

nikita42 — Tue, 31 May 2016 15:51:38 GMT

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?

Re: Using Year function in set analysis

sinanozdemir — Tue, 31 May 2016 16:45:03 GMT

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

Re: Using Year function in set analysis

nikita42 — Tue, 31 May 2016 16:57:18 GMT

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.

Re: Using Year function in set analysis

Anonymous — Wed, 01 Jun 2016 06:48:47 GMT

Hi Nikita,

Use year before GetFieldSelections.

Year(GetFieldSelections(Current HIre Date))

Thanks!

Re: Using Year function in set analysis

sasiparupudi1 — Wed, 01 Jun 2016 07:13:35 GMT

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

Re: Using Year function in set analysis

marcus_sommer — Wed, 01 Jun 2016 07:14:04 GMT

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

Re: Using Year function in set analysis

nikita42 — Wed, 01 Jun 2016 08:40:07 GMT

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

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

Re: Using Year function in set analysis

marcus_sommer — Wed, 01 Jun 2016 09:21:25 GMT

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

Re: Using Year function in set analysis

sasiparupudi1 — Wed, 01 Jun 2016 09:48:43 GMT

Script

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

Re: Using Year function in set analysis

nikita42 — Wed, 01 Jun 2016 10:03:01 GMT

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!

Re: Using Year function in set analysis

marcus_sommer — Wed, 01 Jun 2016 10:41:54 GMT

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