Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator III
Creator III

Date range in the Script

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

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@qlikwiz123  few changes required. see the attched

 

View solution in original post

7 Replies
jwjackso
Specialist III
Specialist III

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

qlikwiz123
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

Screenshot 2020-09-22 153752.pngScreenshot 2020-09-22 154013.png

qlikwiz123
Creator III
Creator III
Author

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

Kushal_Chawda

@qlikwiz123  few changes required. see the attched