Date comparison in set expression to get count

[#1]

Hi,

I have date field in the script and am comparing Order date= today() in the set expression to get number of orders received today, I used below formula but it is not giving anything.

= COUNT({<Date([Order Date],'DD/MM/YYYY') = {">=$(=Today()"}>}[Order Date])

and once I done this I will have to implement orders received this week and orders received in this month as well, pls let me know what is wrong in my formula.

Thanks,

[sunny_talwar]

What format does [Order Date] has?

Try this if [Order Date] is actually a date (dual) and not text:

=Count({<[Order Date] = {">=$(=Date(Today(), 'YourOrderDateFormatHere'))"}>} [Order Date])

[#3]

My order date is in date and time stamp, but I don't want use date and time stamp I just wanted use date part only.

I tried to use below formula, but no luck

=Count({<Date([Order Date],'') = {">=$(=Today()"}>} Date([Order Date],'DD/MM/YYYY'))

[sunny_talwar]

Then I think it would be best to handle this in the script:

LOAD [Order Date] as TimeStamp,

          Date(Floor([Order Date])) as [Order Date]

FROM Source;

Once you do that, you can simply use this expression (Without specifying the format of Order Date this time.

=Count({<[Order Date] = {">=$(=Date(Today()))"}>} [Order Date])

Note: You cannot use functions on the left hand side of the set analysis modifier.

[#5]

I use OLE DB and my script looks like this

SQL SELECT "Order Date",  -- When I try to use Date (Order Date,'DD/MM/YYYY')

'Order ID',

Value

FROM ABC;

it gives me the error.

[sunny_talwar]

Do it on top of your OLE DB Load

Table:

LOAD [Order Date] as TimeStamp,

          Date(Floor([Order Date])) as [Order Date];

SQL SELECT "Order Date",  -- When I try to use Date (Order Date,'DD/MM/YYYY')

'Order ID',

Value

FROM ABC;

[#7]

If I do this I am missing Order ID and Value from the list of the fields

[#8]

or if I want to use like

=Count({<[Order Date] = {">=$(=Date(Today()))"}>} [Order Date])

Date(Today()) should be starting time to end time stamp for the comparison so that my order date will falls between start and end date and time today.

How can I use between operator start date time and end date time today.

[sunny_talwar]

You have to add all those fields:

Table:

LOAD [Order Date] as TimeStamp,

          Date(Floor([Order Date])) as [Order Date],

          [Order ID],

          Value; // add any other you want to bring into your application from the SQL load

SQL SELECT "Order Date",  -- When I try to use Date (Order Date,'DD/MM/YYYY')

'Order ID',

Value

FROM ABC;

[sunny_talwar]

May be this:

=Count({<[Order Date] = {">=$(=TimeStamp(DayStart(Now()))<=$(=TimeStamp(DayEnd(Now())))"}>} [Order Date])

Update: You may be required to specify the timestamp format of Order Date:

=Count({<[Order Date] = {">=$(=TimeStamp(DayStart(Now()), 'OrderDateFormat')<=$(=TimeStamp(DayEnd(Now()), 'OrderDateFormat'))"}>} [Order Date])