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: 
Anonymous
Not applicable

Set analysis - not a number

Hi everyone,

How can I put the condition of "not a number" in a set analysis expression? For example: in below set analysis expression where all Employee IDs are included, I want to change it to only where the Employee ID is not a number (or is a number then I can work out the other way round). (Where the Employee ID is not a number, the values can be a string or blank or some symbols. )

sum({$<[Employee ID]={"*"}>}[Total Net paid])

Any suggestions?

5 Replies
guruprem
Partner - Creator III
Partner - Creator III

You can use a purgechar/keepchar function in script to get the a flag like below

IF ( LEN( purgechar([Employee ID],'0123456789' ) ) > 0 , 1, 0 ) as isEmpwithChar

Then you can use this flag in Measure calculation

arulsettu
Master III
Master III

try this

sum({$<[Employee ID]={"*"} - {'0123456789*'}>}[Total Net paid])



sunny_talwar

May be this:

Sum({$<[Employee ID] = {"=IsText([Employee ID])"}>}[Total Net paid])

Anonymous
Not applicable
Author

Thanks everyone. I figured that this worked:

sum({$<[Employee ID]-={'>0'}>}[Total Net paid])

I'll try your suggestions later.

Anonymous
Not applicable
Author

Yeah it works! Thank you Sunny T!