Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Count(If(WildMatch(Field_Name,'0'),Field_Name))
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
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
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