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: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

Excluding NULL & -ve Values

Hi,

I am trying to do set analysis in Strange table to exclude date which are blank & Day Rem which are in negative.

=Count({<[User ID]={'=Len(Trim([Complete Date]))=0'},[Days Rem]={"<=0"}>}[User ID])

But in not get required result.

1 Solution

Accepted Solutions
sunny_talwar

Your field name is Completed Date and not complete date

=Count({<[User ID]={"=Len(Trim([Completed Date]))=0"},[Day Rem]={"<=0"}>}[User ID])

View solution in original post

10 Replies
aarkay29
Specialist
Specialist

May be this


Count({<[User ID]-={'=Len(Trim([Complete Date]))=0'},[Days Rem]-={"<=0"}>}[User ID])

sunny_talwar

May be this

=Count({<[User ID] = {"=Len(Trim([Complete Date]))>0"}, [Days Rem]={">0"}>} [User ID])

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

No Result

Example Data

 

User IDCompleted DateDay Rem
1231-Dec-1650
124 30
1251-Dec-1620
126 -2
127 -4
1281-Dec-160
129 0
1301-Dec-160
131 -48

I Need count as 4

   

User IDCompleted DateDay Rem
126 -2
127 -4
129 0
131 -48
Anonymous
Not applicable

is your [Days Rem] field really numerical?

you could try

LOAD

...

num(num#([Days Rem])) as [Days Rem],

...

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Yes I did converted dates to dated format and number to number format.

sunny_talwar

Would you be able to share your app?

Anonymous
Not applicable

does every "User ID" have exactly 1 "completed Date"?

DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Please find App And Excel File.

sunny_talwar

Your field name is Completed Date and not complete date

=Count({<[User ID]={"=Len(Trim([Completed Date]))=0"},[Day Rem]={"<=0"}>}[User ID])