topic Re: Date range in the Script in QlikView

Date range in the Script

qlikwiz123 — Fri, 15 Nov 2024 23:56:23 GMT

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

Re: Date range in the Script

jwjackso — Tue, 22 Sep 2020 13:32:44 GMT

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

Re: Date range in the Script

qlikwiz123 — Tue, 22 Sep 2020 13:36:46 GMT

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.

Re: Date range in the Script

Kushal_Chawda — Tue, 22 Sep 2020 14:42:08 GMT

@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

Re: Date range in the Script

qlikwiz123 — Tue, 22 Sep 2020 14:21:32 GMT

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

Re: Date range in the Script

Kushal_Chawda — Tue, 22 Sep 2020 14:41:32 GMT

@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

Re: Date range in the Script

qlikwiz123 — Tue, 22 Sep 2020 14:57:20 GMT

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

Re: Date range in the Script

Kushal_Chawda — Tue, 22 Sep 2020 16:07:28 GMT

@qlikwiz123 few changes required. see the attched