topic Re: Set Analysis for Less than Equal in QlikView

Set Analysis for Less than Equal

Tue, 24 Jul 2012 16:24:56 GMT

Can someone assest me with the Set Analysis for the following fields and varables? It seems so simple and yet all the discussions make it so hard.

EmployeeDirectory.DOH <= vLastYear

Re: Set Analysis for Less than Equal

MayilVahanan — Tue, 24 Jul 2012 16:35:39 GMT

What you are trying to calculate?

For example:

=Count({<EmployeeDirectory.DOH = {'$(=vLastYear)'}>}Distinct EmployeeName)

Edit:

For less than or equal to

=Count({<EmployeeDirectory.DOH = {'<=$(=vLastYear)'}>}Distinct EmployeeName)

Hope it helps

Re: Set Analysis for Less than Equal

CELAMBARASAN — Tue, 24 Jul 2012 16:37:23 GMT

Try this

Sum({<EmployeeDirectory.DOH ={"<=$(=vLastYear)"}>} FieldName)

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 16:38:12 GMT

That is correct but I want to make it Less than or equal too. Does that make since?

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 16:54:29 GMT

How does this look I think you forgot the quotes

Count({<EmployeeDirectory.DOH = {$('>vLastYear')}>} EmployeeDirectory.Name)

Re: Set Analysis for Less than Equal

MayilVahanan — Tue, 24 Jul 2012 16:57:17 GMT

For less than or equal to

=Count({<EmployeeDirectory.DOH = {'<=$(=vLastYear)'}>}Distinct EmployeeName)

Hope it helps

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 17:56:41 GMT

I am calculatinig people who were here in 2011 and I look at

DOH_Year<=vLastYear and NLE_Year<vLastYear

How do I change your expression to do this new calculation?

BTW your calcuation was correct for what I asked for but now I have to include people who were not here at that time as well.

Thank you in advance

Re: Set Analysis for Less than Equal

MayilVahanan — Tue, 24 Jul 2012 18:01:20 GMT

HI,

Is there relation between DOH_Year and NLE_Year? If so, use like this

Edit:

=Count({<DOH_Year = {'<=$(=vLastYear)'}, NLE_Year -= {'<$(=vLastYear)'}>}Distinct EmployeeName)

Can you post a sample file?

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 18:06:31 GMT

NLE is "No Longer Here" and DOH is "Date of hired".

I get that it gets to be DOH_Year<= and NLE_Year <>vLastYear correct?

Re: Set Analysis for Less than Equal

MayilVahanan — Tue, 24 Jul 2012 18:14:05 GMT

Hi,

Use like this,

Is both Year field?If so

=Count({<DOH_Year = {'<=$(=vLastYear)'}, NLE_Year -= {'<$(=vLastYear)'}>}Distinct EmployeeName)

Edit:

Celambarasan solution solve your problem perfectly..I forget that..

=Count({<DOH_Year = {'<=$(=vLastYear)'}> *< NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

Re: Set Analysis for Less than Equal

CELAMBARASAN — Tue, 24 Jul 2012 18:18:55 GMT

For and conditions you have to use like below

=Count({<DOH_Year = {'<=$(=vLastYear)'}>*<NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

It gives you the employees hired <= last year and not here in Last year

Re: Set Analysis for Less than Equal

CELAMBARASAN — Tue, 24 Jul 2012 18:20:06 GMT

I didn't understand your conditions and requirements?Do you need the employees leaved from office last year?

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 21:13:36 GMT

I want everyone who was an employee at the end of year 2011. NLE is No Longer Employeed

Re: Set Analysis for Less than Equal

CELAMBARASAN — Tue, 24 Jul 2012 21:19:23 GMT

=Count({<DOH_Year = {'<=$(=vLastYear)'}>*<NLE_Year = {'>$(=vLastYear)'}>}Distinct EmployeeName)

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 21:29:54 GMT

When I do this I get Zero's. Can you explain what the * is between the two set analysis? And would the NLE_Year be not equal to vLastYear?

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 21:36:14 GMT

Mayil Vahanan Ramasamy

Your calculation worked except it didn't count the blanks from the database, which I want included because they were here at that time. Any ideas why it doesn't count them?

Re: Set Analysis for Less than Equal

MayilVahanan — Tue, 24 Jul 2012 22:02:26 GMT

Hi,

By celambarasan concept is correct,

=Count({<DOH_Year = {'<=$(=vLastYear)'}> *< NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

it checks both, who are all in last year in DOH_Year and NLE_Year also...but exclude the person in last year in NLE_Year..

Can you test like this,

=Count({<DOH_Year = {'<=$(=vLastYear)'}>}Distinct EmployeeName)

=Count({<NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

And Then check like this, before that verify the data manually, if any one is satisfy the following condition..

=Count({<DOH_Year = {'<=$(=vLastYear)'}> *< NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

Which is mentioned earlier, gives exclude the person , who are below the last year in NLE_Year and include the person who are in present year and DOH_Year <= vLastyear..

Hope you understand the expression..

Re: Set Analysis for Less than Equal

Tue, 24 Jul 2012 22:38:20 GMT

Mayil,

Both expression's return all zeros. So it look likes because the first expression is failing for some reason. Any ideas why?

=Count({<NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

=Count({<DOH_Year = {'<=$(=vLastYear)'}> *< NLE_Year = {'<$(=vLastYear)'}>}Distinct EmployeeName)

Re: Set Analysis for Less than Equal

Wed, 25 Jul 2012 15:41:22 GMT

Hi Celambarasan,

Does the >*< mean AND? Does the comma do the same thing?

=Count({<DOH_Year = {'<=$(=vLastYear)'},NLE_Year -= {'<$(=vLastYear)'}>} EmployeeDirectory.Name)

=Count({<DOH_Year = {'<=$(=vLastYear)'}>*< NLE_Year -= {'<$(=vLastYear)'}>} EmployeeDirectory.Name)

Re: Set Analysis for Less than Equal

MayilVahanan — Wed, 25 Jul 2012 16:19:27 GMT

HI,

Did you check the NLE_Year values..

=Count({<NLE_Year = {'2010'}>}Distinct EmployeeName) gives any value..By check this, you can find the error..