topic Re: Date comparison in set expression to get count in QlikView

Date comparison in set expression to get count

Thu, 17 Dec 2015 17:19:09 GMT

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,

Re: Date comparison in set expression to get count

sunny_talwar — Thu, 17 Dec 2015 17:21:16 GMT

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

Re: Date comparison in set expression to get count

Thu, 17 Dec 2015 17:28:37 GMT

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

Re: Date comparison in set expression to get count

sunny_talwar — Thu, 17 Dec 2015 17:31:43 GMT

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.

Re: Date comparison in set expression to get count

Thu, 17 Dec 2015 17:42:24 GMT

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.

Re: Date comparison in set expression to get count

sunny_talwar — Thu, 17 Dec 2015 17:44:38 GMT

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;

Re: Date comparison in set expression to get count

Thu, 17 Dec 2015 17:50:21 GMT

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

Re: Date comparison in set expression to get count

Thu, 17 Dec 2015 17:54:11 GMT

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.

Re: Date comparison in set expression to get count

sunny_talwar — Thu, 17 Dec 2015 17:54:16 GMT

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;

Re: Date comparison in set expression to get count

sunny_talwar — Thu, 17 Dec 2015 17:55:50 GMT

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

Re: Date comparison in set expression to get count

Thu, 17 Dec 2015 18:40:53 GMT

Thanks Sunny this is working fine for me.

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

now I am in the process of comparing past 1 week and I used below one and it worked

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

Re: Date comparison in set expression to get count

Fri, 18 Dec 2015 17:16:35 GMT

Hi,

I am creating few formula's this week assuming starts from Sunday and current month in the set expression,

below are the formula which I have written but its not working as expected. I am not using any variables in the expression.

This Month: =COUNT({$<OrderYr = {$(=Year(Today()))}>,<OrderMth={$(=Month(Today()))}>}Distinct Customers)

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

Can anyone pls correct the formulas.

Thanks,

Re: Date comparison in set expression to get count

sunny_talwar — Fri, 18 Dec 2015 17:20:34 GMT

It would be much easier if you are able to share a sample with the output you are expecting.