Excluding NULL & -ve Values

[DipeshVadgama]

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.

[aarkay29]

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]

No Result

Example Data

User ID	Completed Date	Day Rem
123	1-Dec-16	50
124		30
125	1-Dec-16	20
126		-2
127		-4
128	1-Dec-16	0
129		0
130	1-Dec-16	0
131		-48

I Need count as 4

User ID	Completed Date	Day Rem
126		-2
127		-4
129		0
131		-48

[Anonymous]

is your [Days Rem] field really numerical?

you could try

LOAD

...

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

...

[DipeshVadgama]

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

[sunny_talwar]

Would you be able to share your app?

[Anonymous]

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

[DipeshVadgama]

Please find App And Excel File.

[sunny_talwar · Accepted solution]

Your field name is Completed Date and not complete date

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