show all years between 2 dates

[apthansh]

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.

[Kushal_Chawda]

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

[Tanalex]

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]

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

ID	DATE1	DATE2
1	4/1/2017	4/1/2020
2	3/1/2018	3/1/2020

[apthansh]

@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]

ID	DATE1	DATE2
1	4/1/2017	4/1/2020
2	3/1/2018	3/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 · Accepted solution]

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]

Awesome.This worked.TY MUCH 🙂