Min() - Max() - how to get what's between?

1

Hi

I have a list with months Jan to Dec

The Min month = Jan
The Max month = Mar

How can I get a list that includes the missing months, like this case Feb?
The result I should like to get is: Jan, Feb, Mar

If you have suggestions how and or where to find solutions, it would be great
While searching on Google, I get mainly the approach in Excel, but…

In the doc you see the use case :bulb:

Thanks in advance for your time,
//JP

2

Hi JP,

You should be able to use the sequence function to do this.

First, you’ll need to extract the month number from the start date (Month(Date)), then you can do:

Sequence([Start Month], [End Month],1).FormulaMap(Date([Year],CurrentValue,1)).

Will you need this to work from one year to the next?

If so, it’ll be a bit more complicated, you’ll probably need to work out what the months are as an offset from a specific ‘zero’ date, sequence the months in that way, and then convert them back relative to that ‘zero’ date.

I can help with this, but it will take me more time than I have available right now.

Joe

1 Like
3

Dear @Joe_Innes,

Thanks coming back to my question for what I still didn’t find a solution.
You think very well ahead :brain:, you are right :ok_hand:, I will need to cover more years.:calendar:

In below table I added the month number and the year (with formulas)

image
image.png829×406 12.2 KB

With respect for your time, I will be patient and look forward to your input.

Thank you sooo much,
//JP

4

Sequence(StartDate,EndDate,1).FormulaMap(CurrentValue.ToDate().Year() + ":" + CurrentValue.ToDate().Month()).Unique().BulletedList()

This should give you a bulleted list of Year: Month from StartDate to EndDate

5

If you want actual date values, this will work too:

    .FormulaMap(CurrentValue.DateTimeTruncate("month"))
    .Unique()

Here’s an example:

6 Likes
6

Thank you All
The detailed explanation creates high value for the community and onboarding users.

I am working to get all formulas in a table, with links to the formula pages and I will connect formulas used to content like this and also a relationship to the applicable guidelines.

When it gets shaped, I will share to decide if it would be something to add to the learning section.

Kind regards,
//JP

7

Glad that the folks above provided a solution, and apologies I didn’t get round to it earlier!