topic Syntax for including a date field in a expression in QlikView

Syntax for including a date field in a expression

Wed, 28 Mar 2012 12:32:24 GMT

Hi,

I want to write a expression which counts for a particular day,

for eg. count(sales) , in which i need a include a where condition as date like count(sales) where date = 'today' or date = 'quarter to date"

I need to know the syntax for the same.

Help required on a urgent basis.

Syntax for including a date field in a expression

Wed, 28 Mar 2012 12:51:44 GMT

Hello,

try this:

count({$ <date = {'xyz'} >} sales)

where xyz is the date you desire; you need to insert xyz in the right format according to your source data (do some test).

Regards,

Daniele

Syntax for including a date field in a expression

jagan — Wed, 28 Mar 2012 13:06:22 GMT

Hi,

Try this

count({$ <date = {'$(=Today())'} >} sales)

It gets the count of Sales for current date.

Regards,

Jagan.

Syntax for including a date field in a expression

SunilChauhan — Wed, 28 Mar 2012 14:12:23 GMT

count({<date={'today','quarter to date'}>} sales)

hope this helps

Syntax for including a date field in a expression

Fri, 30 Mar 2012 10:28:39 GMT

Hi all,

In the syntax mentioned, should i use the date as such or i should change it as per my database?

eg, if it is dt, should i change the expression to

count({<dt={'today','quarter to date'}>} sales)

Syntax for including a date field in a expression

Fri, 30 Mar 2012 10:37:02 GMT

Hi,

If I use this expression

count({$<dt={'$03/28/2012'}>} SURGEON)

it counts the total no of values and not considering the date in the count.

Any corrections in the syntax?

Syntax for including a date field in a expression

CELAMBARASAN — Fri, 30 Mar 2012 10:40:29 GMT

Hi,

Try with this

count({$<dt-={'03/28/2012'}>} SURGEON) it excludes the date(03/28/2012) given.

Celambarasan

Syntax for including a date field in a expression

CELAMBARASAN — Fri, 30 Mar 2012 10:43:52 GMT

Hi,

you can use this for your requirement

count({<dt={">=$(QuarterStart(Today()))<=$(Today())"}>} SURGEON)

Celambarasan

Syntax for including a date field in a expression

Fri, 30 Mar 2012 10:54:28 GMT

celambarasan,

It gives the total count and doesnt includes the condition in set analysis. Anything we are missing?

Syntax for including a date field in a expression

CELAMBARASAN — Fri, 30 Mar 2012 10:59:36 GMT

Hi,

Try with distinct

count({<dt={">=$(QuarterStart(Today()))<=$(Today())"}>} Distinct SURGEON)

Celambarasan

Syntax for including a date field in a expression

Fri, 30 Mar 2012 10:59:57 GMT

celambarasan,

count({$<dt-={'03/28/2012'}>} SURGEON) , even this gives the total count. my dt field in database is in varchar format, is that creating a problem to us?

Syntax for including a date field in a expression

Fri, 30 Mar 2012 11:04:25 GMT

Still i use a distinct function, it gives only the total count and not for the particular date

Syntax for including a date field in a expression

CELAMBARASAN — Fri, 30 Mar 2012 11:05:05 GMT

Hi,

Yes.you have to format the date while loading the table from the database.like Date(Date#(Field,Format))

Format is in what format the field in database has may be like 'DD/MM/YYYY'

Celambarasan

Syntax for including a date field in a expression

Fri, 30 Mar 2012 11:23:15 GMT

hi,

I use a sql statement like select * from table1;

now how to include the above said statement? either in sql format or i need to follow the syntax as qlik view format.

Syntax for including a date field in a expression

CELAMBARASAN — Fri, 30 Mar 2012 11:33:03 GMT

Hi,

Yes you can apply precede load to it like

Load

Field1,

Field2,

Date(Date#(Field,Format)) as dateField;

select * from table1;

Celambarasan

Syntax for including a date field in a expression

Fri, 30 Mar 2012 12:26:23 GMT

super cool!!!awesome!!! it works.