Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
bumin
Partner - Creator II
Partner - Creator II

how to count all line with positive value

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
Labels (2)
1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

3 Replies
bumin
Partner - Creator II
Partner - Creator II
Author

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

Anonymous
Not applicable

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
Partner - Creator II
Partner - Creator II
Author

thanks John it works😃