topic Re: How to use date in a filter in QlikView

How to use date in a filter

sylvain_piccarreta — Wed, 17 Oct 2018 12:24:56 GMT

Hello,

I have a variable (Date format) initialized like this

Let vDatesept = date(today()-7,'DD-MM-YYYY');

And I want to create a table which count the number of data in the field TEST where the field CREATED >= vDatesept.

how to do it ?

I try to write :

=COUNT({<CREATED={'>=' & $(vDatesept)}>}DISTINCT(TEST))

=COUNT({<CREATED=vDatesept}>}DISTINCT(TEST))

it doesn't run

Thanks for your help.

Sylvain

Re: How to use date in a filter

MindaugasBacius — Wed, 17 Oct 2018 12:43:51 GMT

What about:

=COUNT({<CREATED={'>=$(vDatesept)"}>}DISTINCT TEST)

Re: How to use date in a filter

stigchel — Wed, 17 Oct 2018 12:58:12 GMT

In load script there is no set analysis, you can use something like this

//Make sure your dateformat is correct/corresponds

SET DateFormat='DD-MM-YYYY';

//Make the variable a num (see also the attached techbrief)

Let vDatesept = Num(date(today()-7));

Data:

Load Date(Data) as CREATED Inline [Data

2-10-2018

9-10-2018

10-10-2018

11-10-2018

12-10-2018

];

Load Count(CREATED) as Count resident Data where CREATED>=$(vDatesept);

Re: How to use date in a filter

sylvain_piccarreta — Wed, 17 Oct 2018 13:54:30 GMT

thanks, but sorry, I was not clear.

I like to show in a table how many TEST by date when date > 10-10-2018 by example

TEST CREATED

#1 02-10-2018

#2 02-10-2018

#3 02-10-2018

#4 09-10-2018

#5 09-10-2018

#6 10-10-2018

#6 10-10-2018 (2 times the samevalue, it's why I need to put DISTINCT)

#7 11-10-2018

#8 12-10-2018

#9 12-10-2018

#10 12-10-2018

#11 12-10-2018

I like a results like this

10-10-2018 1 (because the distinct)

11-10-2018 1

12-10-2018 4

Thanks four your help

Re: How to use date in a filter

stigchel — Wed, 17 Oct 2018 14:22:46 GMT

Back end (load script)

SET DateFormat='DD-MM-YYYY';

Let vDatesept = date(today()-7);

Front end

Expression:

Count({<CREATED={">=$(vDatesept)"}>} DISTINCT Test)

Re: How to use date in a filter

sylvain_piccarreta — Thu, 18 Oct 2018 11:52:06 GMT

thanks