topic Re: Counting zeros in a range in QlikView

Counting zeros in a range

abeyphilip — Mon, 01 Jul 2013 07:17:22 GMT

Hi,

Please see the above excel data. I am struggling with calculation for the "Count of Zeros" column above. It has to calcualate the number of zeros for the months passed for the financial year (Apr, May and Jun). Acitivity is a column based on the "count of zeros" column value.

The values for these months are calcualted values (expressions) and not directly available in my data set.

In excel we have something like - COUNTIF(N6:X6,0), that will give you the count of 0s between N6 and X6.

I checked the different range functions available (Rangecount, rangenumericcount), but none seems to be suitable for this situation. Was wondering if any chart inter record function would do the job.

Would be of great help if you could suggest something on this direction.

Thanks,

Abey

Re: Counting zeros in a range

sivarajs — Mon, 01 Jul 2013 07:25:08 GMT

Count(If(WildMatch(Field_Name,'0'),Field_Name))

Re: Counting zeros in a range

abeyphilip — Mon, 01 Jul 2013 07:51:53 GMT

Thanks Sivaraj.

But this does not work because - my value is based on a calcualate value for "Field_Name".

I will explain a bit more - the values under 'Apr', 'May', 'Jun' are calcuated as sum({<month='Apr',.,.,.>}Field_Name), sum({<month='May',.,.,.>}Field_Name) and so on.

So my field needs to count the zeros across these different expressions.

Thanks,

Abey

Re: Counting zeros in a range

Mon, 01 Jul 2013 08:01:58 GMT

For-- sum({<month='Apr',.,.,.>}Field_Name)

write--

if(sum({<month='Apr',.,.,.>}Field_Name) = 0 , count(sum({<month='Apr',.,.,.>}Field_Name)))

or also write this code

count(if(column1=0,column1))

*********************************

similarly write code for other months

Re: Counting zeros in a range

abeyphilip — Mon, 01 Jul 2013 08:16:49 GMT

Hi Vishwaranjan,

Thanks a lot.

It worked...

count(if(column1=0,column1)) somehow was not giving me the count properly.

Then i used this:

If(column(4)=0,1,0) + If(column(5)=0,1,0) + If(column(6)=0,1,0)

Thanks,

Abey