Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count if with 2 conditional

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.



View solution in original post

15 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

sushil353
Master II
Master II

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

Not applicable
Author

hirenterm.png

Not applicable
Author

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

2. hiring, my code, count(joindate)

3. Termination, still confuse

thanks

senpradip007
Specialist III
Specialist III

Could you please upload sample qvw?

Not applicable
Author

OK, Please wait

Not applicable
Author

jagan
Luminary Alumni
Luminary Alumni

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.



senpradip007
Specialist III
Specialist III

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