7 Replies Latest reply: Dec 6, 2012 7:01 AM by jagan mohan rao appala

# how to get distinct count of No. of orders

Hi,

I wolud like to know the count of unique orders when below conditions get satisfied:

DateKey>=SelectDateFrom and DateKey<=SelectDateTO

My expression is:

COUNT(IF(DateKey>=SelectDateFrom and DateKey<=SelectDateTo,(DISTINCT([Order Count]))))

i do not know, where I am doing wrong.

any idea?

• ###### Re: how to get distinct count of No. of orders

Hi,

Try this expression

=COUNT({<DateKey={'>=\$(=SelectDateFrom)<=\$(=SelectDateTo)'>} DISTINCT [Order Count])

Or you can also try like this.  In you Order table arrive a new column (OrderCount) with value 1 for every record and use the following expression

=Sum({<DateKey={'>=\$(=SelectDateFrom)<=\$(=SelectDateTo)'>} OrderCount)

Because Distinct may cause performance issues.

Hope this helps you.

Regards,

Jagan.

• ###### Re: how to get distinct count of No. of orders

Hi Jagan,

i have to use ur first solution as order count is a test column.

below expression is showing red line when pasted it in Edit Expression.u know what is wrong?

=COUNT({<DateKey={'>=\$(=SelectDateFrom)<=\$(=SelectDateTo)'>} DISTINCT [Order Count])

• ###### Re: how to get distinct count of No. of orders

Hi,

Is SelectDateFrom and SelectDateTo are variables? If they are not variables then post some sample data, so that it would be easier to understand.

Regards,

Jagan.

• ###### Re: how to get distinct count of No. of orders

You missed curly brace before distinct

COUNT({<DateKey={'>=\$(=SelectDateFrom)<=\$(=SelectDateTo)'}> }DISTINCT [Order Count])

• ###### Re: how to get distinct count of No. of orders

Hi jagan,

=COUNT({<DateKey={'>=\$(=SelectDateFrom)<=\$(=SelectDateTo)'>} DISTINCT [Order Count])

Can i know why you are keeping = sign in blue colored area in your expression ? Actually i'm not much aware of this concept.

• ###### Re: how to get distinct count of No. of orders

Sivaraj change did the trick.

Thank you Jagan & Sivaraj.

• ###### Re: how to get distinct count of No. of orders

Hi,