how to count all line with positive value

[bumin]

i would like to count  all line <>0

orig table

dim1	dim2	dim3	total
4000	2174	a1	0
4000	2174	a2	12,46
4000	2174	a3	0
4000	2174	Total	12,46
4000	2699	a4	0
4000	2699	a5	-24,81
4000	2699	Total	-24,81

i want to have

dim1	dim2	count
4000	2174	1
4000	2699	1

[bumin]

the title should be how to count all lines <> 0

[Anonymous · Accepted solution]

Hi Bumin,

It appears your are using a UK number system so I changed the set statements to use US system (replaced commas with periods and periods with commas in the Set statements).

Then, I used this expression below in a straight table with dim1 and dim2 dimensions.

count(distinct(if(total<>0,dim1)))

dim1	dim2	count
4000	2174	1
4000	2699	1

count(distinct(if(total<>0,dim2))) also returned the same result.

count(distinct(if(total<>0,dim3))) returned 2 for both lines.

HTH,

John

[bumin]

thanks John it works😃