Date range in the Script

[qlikwiz123]

I have a date field in my table.

How do I create Last 7 Days, Last 10 Days, Last 15 Days and Last 30 Days Date range for the date field in the script?

Lets say the date is 9/22/2020/ I want Last 7 Days to count from 9/22/2020 - 7 days. Similarly for 10, 15, 30 Days

[jwjackso]

I use Henric's As-Of-Table , it works really well.

[qlikwiz123]

Hi,

That is not my requirement. Henric's method works for Rolling Sum or averages. It does not create Date Ranges on the script level or on UI. 

My requirement is simple. Create Date Range for Last 7, 10, 15, 30 Days for a Date Field I have in the Script itself.

[Kushal_Chawda]

@qlikwiz123  try below

// Assumed your Date field name is "Date" you can use actual field name as per your data

AllDates:
load fieldvalue('Date',recno()) as Date
autogenerate fieldvaluecount('Date');

AsOfDate:
load
Date as AsOfDate,
Date,
'Current Day' as Date_Type
Resident AllDates;

concatenate(AsOfDate)
load
Date as AsOfDate,
Date + 1 - IterNo() as Date,
'Last 7 Days' as Date_Type
Resident AllDates
while IterNo() <= 7;

concatenate(AsOfDate)
load
Date as AsOfDate,
Date + 1 - IterNo() as Date,
'Last 10 Days' as Date_Type
Resident AllDates
while IterNo() <= 10;

concatenate(AsOfDate)
load
Date as AsOfDate,
Date + 1 - IterNo() as Date,
'Last 15 Days' as Date_Type
Resident AllDates
while IterNo() <= 15;

concatenate(AsOfDate)
load
Date as AsOfDate,
Date + 1 - IterNo() as Date,
'Last 30 Days' as Date_Type
Resident AllDates
while IterNo() <= 30;

right join load Date Resident AllDates;

drop table AllDates;

You can now use Date_Type and AsOfDate as selections ro get the expected output

[qlikwiz123]

Hi @Kushal_Chawda 

Even this does not seem to work. It is assigning 'Current Day', 'Last 10 Days', 'Last 15 Days', 'Last 30 Days' to every date in the Date field instead.

So when I select Last 10 Days, it also shows dates from last year

[Kushal_Chawda]

@qlikwiz123  I am not sure you already tried it or not but it is what I am getting  as you can see in below screenshots which is what you need if I understood correctly

[qlikwiz123]

Here is mine. And now I get Table not found error

[Kushal_Chawda · Accepted solution]

@qlikwiz123  few changes required. see the attched