Get result between 2 dates (Script)

[pgalvezt]

Hello,

I would like filter a date and show me the result between 2 dates (Date1 and Date2)

Date(today()-20,'YYYY-MM-DD') ----> This is static date (30-jun-2020) I would like to put a dinamic date into the where section so I can chose any date what I want.

I have a table of ticket available, into this table I have to Dates Date1 (Creation Ticket) Date2(Delivery Ticket)

In my script I have:

Table1:

Load 

person,

ticket,

date1,

date2,

From table

Where Date(today()-20,'YYYY-MM-DD') >= date1 And Date(today()-20,'YYYY-MM-DD') <= Date(If(Isnull(date2) ,'9999-12-31', date2),'YYYY-MM-DD');

[pgalvezt]

Any Help ??

Thank You !

[Kushal_Chawda]

What is the logic to filter?

[pgalvezt]

Hello! 

When you select any day (That in this moment I don´t have a calendar because I don´t have a Date field) 

I need to create a date calendar inside the table. This table have 2 dates, date1 and date 2. If I have a calendar date, Would have filter Year 2020 month Jun day 20 (For example if you call a new date called date3)

Note: Date3 doesn't exist in my table, so actually I have to put  Date(today()-20,'YYYY-MM-DD') but this is static, I need a Dynamic filter.

Where date3 >= date1 And Date(today()-20,'YYYY-MM-DD') <= Date(If(Isnull(date2) ,'9999-12-31', date2),'YYYY-MM-DD');

Thanks!

[pgalvezt]

Any Ideas?

[Kushal_Chawda]

I am still confuse with requirement.  Do you want to filter from script or front end? Based on which condition do you want filter..Look at here

https://community.qlik.com/t5/New-to-Qlik-Sense/interval-data-with-only-one-date-field-to-create-a-t...

[pgalvezt]

Hi,

I want to filter from "Front End" If I select 2020 Jun 30, but based in this condition:

Where date3 >= date1 And Date(today()-20,'YYYY-MM-DD') <= Date(If(Isnull(date2) ,'9999-12-31', date2),'YYYY-MM-DD');

Note: I don't have date3, just I have date1 and date2.  for now my date 3 is Date(today(),'YYYY-MM-DD')

[pgalvezt · Accepted solution]

Got it !

FactTable:
LOAD * INLINE [
Fecha, Venta
01-01-2013,93
02-01-2013,62,
08-01-2013,39,
09-01-2013,94
10-01-2050,63];
TablaMinMax:
LOAD
min(Fecha) as FechaMin,
max(Fecha) as FechaMax
Resident FactTable;
LET vMin=num(Peek('FechaMin',0,'TablaMinMax'));
LET vMax=num(Peek('FechaMax',0,'TablaMinMax'));
CalendarioMaestro:
LOAD
Date(IterNo()+$(vMin)-1) as Fecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);
DROP Table TablaMinMax;

LET vMin=;
LET vMax=;

And then do the where condition by set analysis.