8 Replies Latest reply: Jul 10, 2012 12:17 AM by Kiran Rokkam

# Distinct count total in Straight Table

I have a straight table like below;

Dim1Dim2Dim3Expr1
1........0
1........0
2........1
2........0
2........1
3........1
3........0

TOTAL2

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.

• ###### Re: Distinct count total in Straight Table

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

Regards,

Kiran Rokkam.

• ###### Re: Distinct count total in Straight Table

Hi Kiran,

Thanks for the reply.

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

• ###### Re: Distinct count total in Straight Table

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.

• ###### Re: Distinct count total in Straight Table

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

I've attached a sample qv for your information.

• ###### Re: Distinct count total in Straight Table

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.

• ###### Re: Distinct count total in Straight Table

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.

• ###### Re: Distinct count total in Straight Table

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.

• ###### Re: Distinct count total in Straight Table

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.