Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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])
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'))
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.
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.
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;
If I do this I am missing Order ID and Value from the list of the fields
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.
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;
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])