topic Re: How do I make a calculation with Today() in a Set analyses? in QlikView

How do I make a calculation with Today() in a Set analyses?

Fri, 05 Aug 2016 11:56:29 GMT

I want to count the number of incidences where the datafield ( a date) is BEFORE today. I have tried this

Count({1<Deadline_Date={'<Today()'}>} Deadline_Date)

but it does give me the result

Re: How do I make a calculation with Today() in a Set analyses?

oknotsen — Fri, 05 Aug 2016 13:20:02 GMT

Replace

Today()

$(=Today())

Re: How do I make a calculation with Today() in a Set analyses?

Fri, 05 Aug 2016 13:25:56 GMT

Like this?

Count({<Deadline_Date={$(=Today())}>} Deadline_Date)

It does not work. I want to count the number of dates BEFORE "today"

Re: How do I make a calculation with Today() in a Set analyses?

jyothish8807 — Fri, 05 Aug 2016 13:26:37 GMT

Hi Soren,

Try like this:

Count({<Deadline_Date={'< $(=Today())'}>} Deadline_Date)

Note:Make sure the format of Deadline _date and today() should be in same format eg both DD/MM/YYYY

Regards

Re: How do I make a calculation with Today() in a Set analyses?

Fri, 05 Aug 2016 13:31:15 GMT

Deadline_Date is 22-07-2016 15:00:00

Where do I see the Today() format ?

In the script header?

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

and how do I "align" the formats for DD/MM/YYYY ?

Re: How do I make a calculation with Today() in a Set analyses?

sunny_talwar — Fri, 05 Aug 2016 14:24:38 GMT

You might need to add Date() function as well, in addition to what have been mentioned above:

Count({1<Deadline_Date={"$(='<' & Date(Today(), 'DateFieldFormatHere'))"}>} IncidentID)

Re: How do I make a calculation with Today() in a Set analyses?

Anonymous — Fri, 05 Aug 2016 15:06:51 GMT

Hi,

A little improvement from Sunny:

And pls note you need use double quote to identify it will filter/search for the matching results. single quote is for string with 100% match.

COUNT({<Deadline_Date={"<$(=Date(today(),'DD-MM-YYYY'))"}>}IncidentID)

Format 'DD-MM-YYYY' is an example, you must match this format with your Deadline_Date column.

Zhihong

Re: How do I make a calculation with Today() in a Set analyses?

sunny_talwar — Fri, 05 Aug 2016 15:24:07 GMT

May be make a slight modification in the script:

Date(Floor(Deadline_Date)) as Deadline_Date,

Deadline_Date as Deadline_DateTime

and then try this:

Count({1<Deadline_Date={"$(='<' & Date(Today()))"}>} IncidentID)

Re: How do I make a calculation with Today() in a Set analyses?

idogridish2 — Fri, 05 Aug 2016 15:36:36 GMT

Hi,

You can try

COUNT({$<Deadline_Date={'<$(=Num(Today()))'}>}IncidentID)

Re: How do I make a calculation with Today() in a Set analyses?

oknotsen — Fri, 05 Aug 2016 20:37:55 GMT

I did not say "remove the < or the quotes" .

Re: How do I make a calculation with Today() in a Set analyses?

jyothish8807 — Mon, 08 Aug 2016 18:47:57 GMT

Hi Soren,

Create a field in script:

in script:

Load

Deadline_Date,

Date(floor(Deadline_Date),'DD/MM/YYYY') as Deadline_Date_new

from <>;

In exp:

Count({<Deadline_Date_new={"< $(=date(Today(),'DD/MM/YYYY'))"}>} Deadline_Date)

Regards