Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

show all years between 2 dates

How to show all the years the fall between 2 dates in a list box ? 

EX: Date1: 4/1/2017 and Date2: 4/1/2020

 

I want to show all the yrs b/w these 2 dates in list box:

Date

2017

2018

2019

2020

Thank you much.

 

 

1 Solution

Accepted Solutions
Kush
MVP
MVP

Ideal way would be to generate year in script. But it has limitation. It will not work if user selects any Date1 & Date2 values. For eg. Date1 value of ID=1 & Date2 of ID=2. Both Date selection should be for same ID 

Data:
load ID,
     Date1,
     Date2
FROM Source;

Final:
LOAD *,
year(YearStart(Date1,IterNo()-1)) as Year
Resident Data
While yearStart(Date1,IterNo()-1)<= YearEnd(Date2);

DROP Table Data;

 

 

View solution in original post

8 Replies
Kush
MVP
MVP

Is it static date reference or you have fields like Date1 & Date2 and you select dates from there?

Tanalex
Creator II
Creator II

Conceptually:  YEAR(Date2) - YEAR(Date1)

Example: =YEAR('4/1/2020') - YEAR('4/1/2017'), which returns 3.  Or are you expecting four?

You may need to repalce the Date1/2 with variables, if those are not static.

apthansh
Creator
Creator

@Kush It is not static dates.I have fields Date1 & Date2.

 

IDDATE1DATE2
14/1/20174/1/2020
23/1/20183/1/2020

 

apthansh
Creator
Creator

@Tanalex I do not want the year difference b/w 2 dates..I want to show the years that fall between 2 dates.

Kush
MVP
MVP

Create two variable as below on frond end

vMinYear
=year(min(Date1))

vMaxYear
=year(max(Date2))

 

then you can create below expression in filter

=ValueLoop(vMinYear,vMaxYear, 1)

 

Tanalex
Creator II
Creator II

IDDATE1DATE2
14/1/20174/1/2020
23/1/20183/1/2020

 

ID 1 = 3 year difference

ID2 = 2 Year difference

Look at adding the logic that is best, in the load script.  HTH

Kush
MVP
MVP

Ideal way would be to generate year in script. But it has limitation. It will not work if user selects any Date1 & Date2 values. For eg. Date1 value of ID=1 & Date2 of ID=2. Both Date selection should be for same ID 

Data:
load ID,
     Date1,
     Date2
FROM Source;

Final:
LOAD *,
year(YearStart(Date1,IterNo()-1)) as Year
Resident Data
While yearStart(Date1,IterNo()-1)<= YearEnd(Date2);

DROP Table Data;

 

 

View solution in original post

apthansh
Creator
Creator

Awesome.This worked.TY MUCH 🙂