Count in a given year range

[User12321]

Hi,

I have a table with fields Supplier, StartYear and EndYear.

I want to count the number of suppliers based on the max year. 

Any advise please. Thank you.

[Vegar]

Try this

Count({<StartYear={">=$(=max(Year))"},  EndYear={">=$(=max(Year))"}>}Supplier)

[User12321]

Hi @Vegar ,

Your solution is not able to capture those eg. StartYear=2021 and EndYear=2022.

Any other suggestion please.

[tresesco]

May be with OR in set analysis, like:

=Count({<StartYear={'2021'}>+<EndYear={'2021'}>}Supplier)

[User12321]

Hi @tresesco ,

If my StartYear=2020 and EndYear=2022, will your suggestion still work?

Or do I need to create another column in load with all the years and then see if my max year is inside the years. However, I am not sure how to do this. 

[tresesco]

No need to get all values. We have to just try to understand your logic and then it should be doable, may be like:

=Count({<StartYear={">=2021"}>+<EndYear={">=2021"}>}Supplier)

Which means if either of star and end year is >= then should be counted.

[User12321]

Hi @tresesco,

StartYear 2022 and EndYear2022 is also being counted. 

What else should I try?

[tresesco · Accepted solution]

=Concat({<StartYear={"<=2021"}, EndYear={">=2021"}>}Supplier)