topic Re: how to create date range which selects all dates within range in QlikView

how to create date range which selects all dates within range

sarahshong — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik Community,

This is not a specific problem, but looking for route to a solution for a dilemma I am having with my date.

I am pulling a table that includes a list of about 1000 dates. Each of the dates are associated with a monetary amount. I want to create a range that takes every date and 10 dates before that date (when dates are in order). I have created a label using mapping to find the 10th date prior to a given date (Prev_Date). Then i used that create a label for the 10 day range.

ApplyMap('Prev_Date_Map',DATE,'Unmapped') as PREV_DATE

ApplyMap('Prev_Date_Map',DATE,'Unmapped')&'-'&Date(DATE,'MM/DD/YYYY') as Date_Range

my goal is to make it so that when I select a date range, a list box for 'DATE' will select all the dates that fall within this range.

so if Date_Range = 1/1/2017-1/1/10/2017 then the list box would bring up

1/1/2017

1/2/2017

1/3/2017

1/4/2017

1/5/2017

1/6/2017

1/7/2017

1/8/2017

1/9/2017

1/10/2017

In the example I am using all available dates in order, but my data set has missing date so I will need to pull up dates within the range that exist in the dataset.

Not sure which route I need to go. I've contemplated doing something with set analysis within the front end, creating a separate table within the load, or possibly a trigger. But i'm fairly new to Qlikview so would appreciate some advice.

Thanks!

Re: how to create date range which selects all dates within range

Clever_Anjos — Thu, 28 Sep 2017 18:17:40 GMT

Use a As-Of Table The As-Of Table with a flag for last 10 days

Re: how to create date range which selects all dates within range

sarahshong — Wed, 04 Oct 2017 17:18:08 GMT

Can you explain what you mean by a "flag for the last 10 days"?

Re: how to create date range which selects all dates within range

Frank_Hartmann — Wed, 04 Oct 2017 17:31:38 GMT

this may be helpful

ApplyMap

Re: how to create date range which selects all dates within range

Clever_Anjos — Wed, 04 Oct 2017 17:32:23 GMT

PFA

Re: how to create date range which selects all dates within range

maxgro — Wed, 04 Oct 2017 18:46:44 GMT

TableWithDate:

load

Date(Today()-RecNo()) as date,

Rand() as Value

autogenerate 1000;

DatesGroup:

LOAD

date(date-9) & '-' & date as [10 dates],

date(date - iterno() +1) as date

Resident TableWithDate

while IterNo() <= 10

;