Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
the title should be how to count all lines <> 0
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
thanks John it works😃