I have a text column with this value:
2018 - 2021
I would like to find an expression that can calculate the years between this range.
The output should be an EnumList:
2018, 2019, 2020, 2021
Set up a Table that is just a list of years.
Then pull a matching List from that:
FILTER(
year ,
AND(
[year] <= NUMBER( INDEX( SPLIT( [text] , " - " ) , 2 )
[year] >= NUMBER( INDEX( SPLIT( [text] , " - " ) , 1 )
)
)
Thank you very much @Marc_Dillon. Instead of using an extra table, could I also hardcode the years list in the expression?
I can’t think of any way. Because you need to filter an existing list of dates, by comparing against your start and end. There is no way to do something like this:
FILTER(
LIST(x,y,z)
...
)
Like this?
TOP(
LIST(
(YEAR([Date]) - 0),
(YEAR([Date]) - 1),
(YEAR([Date]) - 2),
...,
(YEAR([Date]) - N),
),
M
)
Where N is the most number of years you’ll allow, M is the number you need now, and M <= N.
Thank you @Marc_Dillon and @Steve these both very good solutions.
User | Count |
---|---|
26 | |
25 | |
25 | |
18 | |
17 |