New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

counting records between two values

i need to count the records between two values, heres what i got and its erroring on me

count(if((mid([PIID],10,1)<>'G') and ([Base And Alloptions Amnt]'>150000<=50000') and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID],6)),[Contr Office ID]))

can someone please tell me what i'm doing wrong.

thank you

1 Solution

Accepted Solutions
MVP

Re: counting records between two values

Try this

Count(If((Mid([PIID], 10, 1) <> 'G') and ([Base And Alloptions Amnt] > 150000 and [Base And Alloptions Amnt] <=500000) and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID], 6)), [Contr Office ID]))

7 Replies
MVP

Re: counting records between two values

May be try this

count(if((mid([PIID],10,1)<>'G') and ([Base And Alloptions Amnt] > 150000 and [Base And Alloptions Amnt] <=50000') and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID],6)),[Contr Office ID]))

or using set analysis

Count({<PIID -= {"?????????G*"}, [Base And Alloptions Amnt] ={">150000<=50000"}, [Mod Number] = {'0'}, [Contr Office ID] = {"=[Contr Office ID] = Left([RefIDV PIID], 6)"}>} [Contr Office ID])

New Contributor II

Re: counting records between two values

i'm not familar with set analysis, so for now i would need to stick with the first example you provided. however it seems like when the record is less than 500K it includes those values that are under 150K and the opposite happens when it greater than 150K it includes values greater that 500K so i'm cound records that i shouldn't

that is why i'm trying to figure out the > or <= in my example.

thanks

MVP

Re: counting records between two values

What exactly is the condition you are looking for [Base And Alloptions Amnt]?

New Contributor II

Re: counting records between two values

i need to count all records that are > 150000 and are <= 500000

New Contributor II

Re: counting records between two values

oh and yes [Base and Alloptions Amnt] is the field

MVP

Re: counting records between two values

Try this

Count(If((Mid([PIID], 10, 1) <> 'G') and ([Base And Alloptions Amnt] > 150000 and [Base And Alloptions Amnt] <=500000) and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID], 6)), [Contr Office ID]))

New Contributor II

Re: counting records between two values

it works thank you!!! you ROCK!