topic Re: Count if with 2 conditional in QlikView

Count if with 2 conditional

Mon, 22 Sep 2014 02:58:07 GMT

dear all

how are u today, now i have some problem, if u have time, would u like to help me, thanks

current , i have data

name joindate termindate status

rika sang 2010-12-12 2014-12-12 In-Active

andrias 2011-12-12 2014-12-12 In-Active

anita 2012-12-12 2014-12-12 In-Active

ambra 2010-12-12 2013-12-12 In-Active

andrian 2014-12-12 null Active

so i want to know about Using 2 conditional ( date and Status)

1. count joindate person every year

2. count termindate person every year

i using this code for item (2) --> count({<[e_status]={'In-Active'}>} e_status )

- but i want to using 2 condition, is termindate. i don't know , how to write in expression

would u like to helpme

thanks

Re: Count if with 2 conditional

jagan — Mon, 22 Sep 2014 03:49:04 GMT

Hi,

Try like this

Script:

LOAD

Year(joindate) AS JoinYear,

Year(termindate) AS TerminatedYear

FROM DataSource;

Now in set analysis try like this

count({<[e_status]={'In-Active'}, TerminatedYear={2014}>} e_status )

Hope this helps you.

Regards,

Jagan.

Re: Count if with 2 conditional

sushil353 — Mon, 22 Sep 2014 04:23:04 GMT

Hi,

This issue can also be resolved using island calender..

Make a master calender independent of the data model..

also make a year from join date and terminate date.. say j_year and t_year ...

Then in your expression you can write..

count({<[e_status]={'In-Active'},t_year = p(Year)>} e_status) -- here Year is coming from master calender.

similarly for joining year

count({<[e_status]={'In-Active'}, j_year = p(Year)>} e_status)

HTH

Sushil

Re: Count if with 2 conditional

Mon, 22 Sep 2014 04:44:21 GMT

Re: Count if with 2 conditional

Mon, 22 Sep 2014 04:46:16 GMT

1. base on my data, how to make parameter year ( on top dashboar)

2. hiring, my code, count(joindate)

3. Termination, still confuse

thanks

Re: Count if with 2 conditional

senpradip007 — Mon, 22 Sep 2014 04:50:10 GMT

Could you please upload sample qvw?

Re: Count if with 2 conditional

Mon, 22 Sep 2014 04:58:47 GMT

OK, Please wait

Re: Count if with 2 conditional

Mon, 22 Sep 2014 05:00:01 GMT

Re: Count if with 2 conditional

jagan — Mon, 22 Sep 2014 05:01:51 GMT

Hi,

Try like this

Script:

Data:

LOAD

name,

joindate AS Date,

Year(joindate) AS Year,

status

FROM DataSource

WHERE status = 'Active';

Concatenate(Data)

LOAD

name,

termindate AS Date,

Year(termindate) AS Year,

status

FROM DataSource

WHERE status = 'In-Active';

Now use Year as filter for Year (which is common for both join & Terminate) and use below expressions

Hire:

count({<[e_status]={'Active'}, Year={2014}>} name)

Terminate:

count({<[e_status]={'In-Active'}, Year={2014}>} name)

If this not works attach some sample file and your explain what you are trying to do with some sample output.

Regards,

Jagan.

Re: Count if with 2 conditional

senpradip007 — Mon, 22 Sep 2014 05:20:43 GMT

Your application is not opening. Did you use section access?

Re: Count if with 2 conditional

Mon, 22 Sep 2014 06:07:47 GMT

no, i don't

Re: Count if with 2 conditional

Mon, 22 Sep 2014 06:10:34 GMT

i 'll try, thanks

Re: Count if with 2 conditional

Mon, 22 Sep 2014 06:11:11 GMT

Re: Count if with 2 conditional

Mon, 22 Sep 2014 07:17:10 GMT

Hire:

count({<[e_status]={'Active'}, Year={2014}>} name)

Terminate:

count({<[e_status]={'In-Active'}, Year={2014}>} name)

about Hire

how know , if i want to know count join date, maybe one of person in this year (2014), has resigned

( based on my data)

Re: Count if with 2 conditional

jagan — Mon, 22 Sep 2014 07:21:10 GMT

Hi,

Try like this

Script:

Data:

LOAD

name,

joindate AS Date,

Year(joindate) AS Year,

status

FROM DataSource

WHERE status = 'Active';

Concatenate(Data)

LOAD

name,

termindate AS Date,

Year(termindate) AS Year,

status,

joindate

FROM DataSource

WHERE status = 'In-Active';

Regards,

Jagan.

Re: Count if with 2 conditional

Mon, 22 Sep 2014 09:10:35 GMT

i try and success, thanks