topic Greater than today date in QlikView

Greater than today date

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've a requirement where I need to show the count where the dates are greater than today.

I've a field name ENDDDATE and I've to count the ENDDATE where it is greater than today date.

I've tried count({<Date={">=Today())>ENDDATE"}>}distinct( Dimension))

and

Count({$<ENDDATE={">=$(date(Today())"} >} Dimension) but the count doesn't matches for some reason.

Can anyone please help?

And my DateFormat='YYYY-MM-DD' both in script and master calendar.

stalwar1‌swuehl‌loveisfail‌

Re: Greater than today date

swuehl — Mon, 03 Oct 2016 14:31:10 GMT

Please have a look at

Dates in Set Analysis

Why don’t my dates work?

If you want to evaluate a function in dollar sign expansion, use a leading equal sign:

Count({$<ENDDATE={">=$(=date(Today(),'YYYY-MM-DD')"} >} Dimension)

and make sure that the ENDDATE field format matches the format of your formatted today's date

edit: Changed date format

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 14:31:50 GMT

Try this:

Count({$<ENDDATE = {">=$(=Date(Today(), 'YYYY-MM-DD'))"}>} Dimension)

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:38:53 GMT

Some of the values in my ENDDATES filed are for future

EX: 2017-05-12

2017-08-21

So I want to count the future dates only

I've 4 month year

June-2016

July-2016

Aug-2016

Sep-2016

There is also a field STARTDATE but I only wanted to count those dates which has already passed from today.

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 14:40:40 GMT

Did this not work?

Count({$<ENDDATE = {">=$(=Date(Today(), 'YYYY-MM-DD'))"}>} Dimension)

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:42:42 GMT

No. The count is not matching with the end dates field.

It is also selecting 2016 dates

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:45:00 GMT

Attached is my Master calendar script and

I've changed the

SET DateFormat='YYYY-MM-DD'; in Main Tab of Script.

And my Date format in xl file are below.

2017-06-21

2017-06-22

2017-08-29

2017-09-26

2016-06-20

2016-06-24

Please let me know if i've to make any changes to it?

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 14:45:09 GMT

You want it to count everything greater than or equal to Oct 3rd 2016, right? Nov/Dec 2016 will still be counted? Or do you want to count stuff from 2017 onward?

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:45:50 GMT

I tried it and it doesn't work. Your expression is similar to Sunny's expression.

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:46:57 GMT

Greater than Today I want to count...

For today it should be > Oct3rd

For tomorrow it should be > Oct4th and so on...

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 14:48:38 GMT

What exactly are you hoping from the script?

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 14:50:37 GMT

I just gave you my Master Calendar script to verify if I've done it correctly?

But my aim is to show the Count of dates > today

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 14:52:45 GMT

Got it, I think it would be more helpful if you can share a sample with the expected output

Preparing examples for Upload - Reduction and Data Scrambling

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 15:07:03 GMT

My test qvw @ stalwar1‌

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 15:10:49 GMT

So the chart we are seeing (bar chart) isn't right? What do you want to see instead?

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 15:18:38 GMT

The counts for employee in bar chart is not matching the Enddates filter on the right.

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 15:20:16 GMT

If you select the first employee, the bar chart count is 19 and the Enddate count in filter is 11

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 15:23:22 GMT

Are you looking for a DISTINCT count of ENDDATE may be?

Count({$<ENDDATE = {">=$(=Date(Today(), 'YYYY-MM-DD'))"}>} DISTINCT ENDDATE)

Re: Greater than today date

MK9885 — Mon, 03 Oct 2016 15:28:32 GMT

I tried with the above expression you gave but it still doesn't matches the EndDate count.

Because all of the enddates are for 2017.

Re: Greater than today date

sunny_talwar — Mon, 03 Oct 2016 15:34:04 GMT

arvind654 -

Bro, I am confused

1) I don't see 19 for the first Employee, I am seeing 18 instead. After using the new expression I see 9 which matches the end dates after today

Now can you explain me what exactly are you looking to see