Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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.

 

 

Labels (7)
1 Solution

Accepted Solutions
Kushal_Chawda

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
Kushal_Chawda

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
Author

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

 

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

 

apthansh
Creator
Creator
Author

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

Kushal_Chawda

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

Kushal_Chawda

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;

 

 

apthansh
Creator
Creator
Author

Awesome.This worked.TY MUCH 🙂