Distinct count total in Straight Table

[#1]

I have a straight table like below;

Dim1	Dim2	Dim3	Expr1
1	....	....	0
1	....	....	0
2	....	....	1
2	....	....	0
2	....	....	1
3	....	....	1
3	....	....	0
		TOTAL	2

Dim1, Dim2 and Dim3 are Dimensions and Expr1 is an expression.

Expr1 takes the value of 1 or 0.

Expr1 can be 1 for more than once per Dim1 (eg, Dim1 =2 has Expr1=1 twice)

Please help me to get the  distinct count of Dim1 when Expr1=1 and display it in  the Total Row.

(Total value does not need to be in the same column as Expr1, adding another expression is acceptable)

Thanks in advance.

[#2]

Change expr1 with Count(distinct if(aggr(expr1,dim1,dim2,dim3) =1,dim1,0)).

Regards,

Kiran Rokkam.

[#3]

Hi Kiran,

Thanks for the reply.

Dim2 and Dim3 are calculated dimensions and the labels didn't accept in the expression.

[#4]

Can you add those expressions for dim2 and dim2 in the expressions? If that doesnt work add the fields used in their calculation into the expression.

Kiran.

[#5]

Thanks, it helped to make the expression work, but it displays 1s in every row.

I've attached a sample qv for your information.

[#6 · Accepted solution]

Just change it to:

=Count(DISTINCT if(Aggr(Expr1,Dim1,Dim2,Dim3)=1,Dim1)).

Notice the else part of if condition removed.

Regards,

Kiran Rokkam.

[#7]

Thank you so much Kiran, it worked.

Well, it worked in the sample qv but not in the actual one 😞 . I'll see what I have missed here. My wild guess is the calculated dimensions. I've tried it replaced by the actual expressions instead of column names but still no luck. I'll play around with it since your expression is working fine for the sample.

Thanks you for your time.

[#8]

After some testing I found that the culprit is sort order of columns. In my expression, I use above() and below() functions and hence, the dimensions are ordered in a particular way. To my surprise, Aggr() function is not affected by the sort properties of the table (at least in this case), instead, I believe it uses load sort order.

I've attached the sample qv; it contains every possiblity I can think of, calculated dimensions, variables, above() function etc.

[#9]

Aggr() creates a temp table and hence would not be effected with table properties. Only the dimensions will matter for aggr() since its values are rolled up to them.

Regards,

Kiran Rokkam.