topic Re: Count of records Greater than or Equal to today in QlikView

Count of records Greater than or Equal to today

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

Hello,

I'm trying to put together a bar graph to show the number of trainees we have starting on any given day. The graph currently works when using an IF statement but I cannot get it working using set analysis. I inevitably want to make it so the graph shows a rolling time frame of today --> today + 14 and ignore any of the filters plus make it read only. I tried detaching, but of course it comes right back after a reload (what's the point of being able to detach if it's just going to reattach).

TStartDate is stored as a date field, so I shouldn't have to do a '=Num(Today())'.

This is what currently works:

=If(TStartDate <= today()+14 and TStartDate >= today(), count(TCode))

This is what I feel should work but does not:

=Count({<TStartDate={">=$(=Date(Today()))"},TStartDate={"<=$(=Date(Today()+14))"}>} TCode)
=Count({1<TStartDate={">=$(=Date(Today()))"},TStartDate={"<=$(=Date(Today()+14))"}>} TCode)

Any ideas? I feel like I've tried every variation of what has worked on past fields.

Thank you

Re: Count of records Greater than or Equal to today

varshavig12 — Fri, 26 Aug 2016 05:20:35 GMT

Try this:

Count({<TStartDate={">=$(=Date(Today())) <=$(=Date(Today()+14))"}>} TCode)

Re: Count of records Greater than or Equal to today

sunny_talwar — Fri, 26 Aug 2016 09:21:39 GMT

What is your date format here? Can you share the script that you use to create TStartDate in the script?

Dates in Set Analysis

Having said that, I would try something like this:

=Count({<TStartDate={"$(='>=' & Date(Today(), 'DateFieldFormatHere') & '<=' & Date(Today()+14, 'DateFieldFormatHere'))"}>} TCode)

Here DateFieldFormatHere is a placeholder and needs to be replaced with the TStartDate field's format

Re: Count of records Greater than or Equal to today

Anonymous — Fri, 26 Aug 2016 19:12:55 GMT

Thanks Sunny! This is what I used to get it working:

=Count({1<TStartDate={"$(='>=' & Date(Today(), 'M/D/YYYY') & '<=' & Date(Today()+14, 'M/D/YYYY'))"}>} TCode)

What's your take on when to know the difference between using a format like this (used in other places):

TStartDate={">=$(=Date(Today()))"}

Compared to :

TStartDate={"$(='>=' & Date(Today(), 'M/D/YYYY'))"}

Not knowing the syntax difference between where to put the '$', whether to use quotes around the >=, whether you need to specify a date format, etc., has been a difficult learning point for me.

Re: Count of records Greater than or Equal to today

sunny_talwar — Sat, 27 Aug 2016 13:26:52 GMT

I like to use my expression this way

=Count({1<TStartDate={"$(='>=' & Date(Today(), 'M/D/YYYY') & '<=' & Date(Today()+14, 'M/D/YYYY'))"}>} TCode)

But this should give you the same result

=Count({1<TStartDate={">=$(=Date(Today(), 'M/D/YYYY'))<=$(=Date(Today()+14, 'M/D/YYYY'))"}>} TCode)

The reason I use it the way I do is because, I can test my set analysis input in a text box, by simply picking everthing between the dollar sign expansion and put it into a text box object -> ='>=' & Date(Today(), 'M/D/YYYY') & '<=' & Date(Today()+14, 'M/D/YYYY').The above should give you the range of date you want and in the date format you have for TStartDate. If one of the two isn't right, your set analysis will most likely won't work for you.

Now to answer when to use the date format vs. when not to use is based on the fact how you have created your field in the script. If you provided a format while creating a date or you did not even use a date format, but QlikView was intelligent enough to understand it as a date then you will need the date format (unless it matches the date format within the environmental variable). I will give few example below to explain what I just meant. Let say your environmental variable is set like this

SET DateFormat='DD/MM/YYYY';

LOAD TStartDate as Date,

Date(TStartDate) as Date1,

Date(TSTartDate, 'M/D/YYYY') as Date2

Now in the above scenario, you would need to specify the date format for Date and Date2, but you would not need to specify the date format for Date1 because Date1 is not assigned DD/MM/YYYY and the same would be true on the front end when you will do Date(Today()) which will be assigned DD/MM/YYYY.

Does this help?

Best,

Sunny