how to get distinct count of No. of orders

[#1]

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?

Thanks in advance.

[jagan · Accepted solution]

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.

[#3]

Hi Jagan,

thanks for ur quick reply.

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])

[#4]

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.

Thanks in advance.

[jagan]

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.

[sivarajs]

You missed curly brace before distinct

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

[#7]

Sivaraj change did the trick.

Thank you Jagan & Sivaraj.

[jagan]

Hi,

Please close this discussion if your problem solves.

Venkat :

For Date fields we need to put =.

Regards,

jagan.