topic Filtering rows with IF expression in which one value does not exist in QlikView

Filtering rows with IF expression in which one value does not exist

Fri, 23 Aug 2013 11:49:36 GMT

Hi!

I'm trying to build and expression to find out all the people who do not finished any courses for which they get CourseCredits. In my data model the CourseCredits field is linked to many statuses (such as failed and planned) and so I need to filter out all the rows with the Status 'Done'.

I tried the following expression but it just filters out the Status values 'Done' whereas I want to filter all the people who do not have Status 'Done':

= sum(if(Status <> 'Done', CourseCredits, 0))

Could somebody help me out what I should use here?

All the best,

Mikael

Re: Filtering rows with IF expression in which one value does not exist

narender123 — Fri, 23 Aug 2013 11:55:02 GMT

Hi,

Try this.

= if(Status <> 'Done', Sum(CourseCredits), 0)

also in Total Mode

check "Sum og Rows"

Thanks.

Re: Filtering rows with IF expression in which one value does not exist

Gysbert_Wassenaar — Fri, 23 Aug 2013 12:13:56 GMT

This filters out all the PersonID's that do not have a status Done, leaving only those persons that finished at least one course:

sum({<PersonID=p({<Status={'Done'}>}PersonID)>} CourseCredits)

This filters out all the PersonID's that do have a status Done, leaving only those persons that did not finish any course:

sum({<PersonID=e({<Status={'Done'}>}PersonID)>} CourseCredits)

Replace PersonID with the field that identifies your persons.