topic Re: Expression Help (Count) in QlikView

Expression Help (Count)

ajaysingh29 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Need your help, I have a pivot table in which I wanted to display count of +ve/-Ve values & actual sum in third column.

I am using following formula in expression :

=count({$<[ Amount]={'<0'}>}[ Amount]) &

=count({$<[Amount]={'>0'}>}[ Amount])

However its showing me values which are $-0 & $0.00. I don't want to consider these records in my pivot object.

Thanks in advance.

Re: Expression Help (Count)

avinashelite — Tue, 21 Feb 2017 05:41:16 GMT

Try like this

Go To chart properties > Presentation tab >Check the Suppress Zero values

That should solve the problem

Re: Expression Help (Count)

sdmech81 — Tue, 21 Feb 2017 05:43:09 GMT

May be this:

=count({$<[ Amount]={'<0'},Amount]-={'-0'}>}[ Amount])

=count({$<[Amount]={'>0'},Amount]-={'0.00'}>}[ Amount])

Try

Re: Expression Help (Count)

Tue, 21 Feb 2017 06:06:47 GMT

count({<sign([ Amount])={-1}>}<[ Amount])

count({<sign([ Amount])={1}>}<[ Amount])

this will not consider 0 values

Re: Expression Help (Count)

ajaysingh29 — Tue, 21 Feb 2017 07:52:26 GMT

Thank you Manoj & All for your time n reply!

Re: Expression Help (Count)

avinashelite — Tue, 21 Feb 2017 07:59:47 GMT

I don't think the above expression will work !!! could share the sample app where its working for you ???

Re: Expression Help (Count)

avinashelite — Tue, 21 Feb 2017 08:07:22 GMT

sdmech81‌ did Monaj expressions work's for you ??

Re: Expression Help (Count)

sdmech81 — Tue, 21 Feb 2017 08:24:08 GMT

Hi Avinash,

Not infront of machine..

I think it should work bcs sign will return -1,0,1 ..

Then when he says sign(AMT)=-1 it means all negative values other even excluding zeros..

Ll confirm Avinash once infront of machine.. Thanks

Re: Expression Help (Count)

avinashelite — Tue, 21 Feb 2017 09:18:49 GMT

I don't think so , expression at both the ends in set analysis don't work

Re: Expression Help (Count)

sdmech81 — Tue, 21 Feb 2017 09:28:27 GMT

avinashelite‌,

Just put the same in text object but I am getting error with this

count({<sign([ Amount])={-1}>}[ Amount])

Hope u also did the same ..

@AJay, Can u Plss paste expression,which u used??

Sachin

Re: Expression Help (Count)

avinashelite — Tue, 21 Feb 2017 09:53:23 GMT

Exactly that's what I am trying to mention on either side of the = we cannot have an expression in Set Analysis this seems to be fishy for me manojqlik‌ could share the app where its working and how it will work ?

Re: Expression Help (Count)

sdmech81 — Tue, 21 Feb 2017 09:57:32 GMT

@Ajay, Please share the screen/Example how it worked for U??

I did try but it didn't work ..

Sachin..

Re: Expression Help (Count)

Tue, 21 Feb 2017 12:45:54 GMT

Count({< [Amount] = {'>0'} >} [Amount])---Positive Values

Count({< [Amount] = {'<0'} >} [Amount])--Negative Values

Re: Expression Help (Count)

sunny_talwar — Tue, 21 Feb 2017 13:00:09 GMT

Hahahaha strange that it worked for Ajay

Re: Expression Help (Count)

Tue, 21 Feb 2017 14:23:50 GMT

Hi experts,

I did not test it , whether it is working fine or not

Randomly I have given the reply ,

If I get sample data will test it .

Re: Expression Help (Count)

Tue, 21 Feb 2017 14:27:02 GMT

I request Ajay Singh to explain how it helped him to achieve the result