Modify variable by selected option from combobox

[Calin_Mateut]

Hello everyone! I have a collection with 36 rows containing the months of 3 years (2022, 2023, 2024).

How can I make it so that when the value is 2023 and I want to choose 2022 my varMonthInd should decrease -12, and when I choose 2024 my varMonthInd +12 should increase?

The idea is that when I choose a year lower than the one already selected, my varMonthInd should decrease by -12 and when I choose a higher one, my varMonthInd should increase by +12. There is also the rule that the decrease should not exceed the value 1 (the first value in the collection) and the increase should not exceed CountRows (colMonthYear2 - the maximum value in the collection) You can see varMonthInd value in black on blue background (#13)

Please see the photos! What formula should I use? Many thanks!

[madlad]

I'm assuming if you go up or down 2 years, you'll want to modify by 24 months instead of 12. I'm also assuming that if the varMonthInd is less than 13, you're in 2022, if its between 13 and 24 you're in 2023, and if its over 24, you're in 2024.

One thing you could do is:

in the OnChange of the years combobox, you could use something along the lines of: 

If(varMonthInd > 24, 
  Set(varMonthInd, varMonthInd - (2024 - Self.Selected.Value)*12),
  varMonthInd > 12,
  Set(varMonthInd, varMonthInd - (2023 - Self.Selected.Value)*12),
  Set(varMonthInd, varMonthInd - (2022 - Self.Selected.Value)*12)
)

You may have to make a couple changes to this, but this should add or subtract based on the previous value compared to the current value.

Another idea I had, is that if varMonthInd is based off of both the month and year dropdowns, you could LookUp the row number of the row containing the correct month and year in the ColMonthsYear2 collection.

Hope this helps!

[Calin_Mateut]

Thank you @madlad !  I did something similar to the idea you suggested and it worked for me for a year, it works. I put the code below.

Do you know any solution for the month? I have the following problem. If the year 2024 and January is selected, the value of the variable is 25, but if I change it to February, I change my variable to 2 instead of 26. Do you have any idea how I can solve this? Thanks!

// when selected year is Year(Now())+1

If(

    varMonthInd >= 25 && varMonthInd <= 36 && YearsDropdown_1.Selected.Value= Year(Now()),

    UpdateContext({ varMonthInd: varMonthInd - 12 }),

    If(

        varMonthInd >= 25 && varMonthInd <= 36 && YearsDropdown_1.Selected.Value=Year(Now())-1,

        UpdateContext({ varMonthInd: varMonthInd - 24 })

    )

);

// when selected year is Year(Now())

If(

    varMonthInd >= 13 && varMonthInd <= 24 && YearsDropdown_1.Selected.Value=Year(Now())-1,

    UpdateContext({ varMonthInd: varMonthInd - 12 }),

    If(

        varMonthInd >= 13 && varMonthInd <= 24 && YearsDropdown_1.Selected.Value=Year(Now())+1,

    UpdateContext({ varMonthInd: varMonthInd  +12 })

    )

);

// when selected year is Year(Now())-1

If(

    varMonthInd >= 1 && varMonthInd <= 12 && YearsDropdown_1.Selected.Value=Year(Now()),

    UpdateContext({ varMonthInd: varMonthInd + 12 }),

    If(

        varMonthInd >= 1 && varMonthInd <= 12 && YearsDropdown_1.Selected.Value=Year(Now())+1,

    UpdateContext({ varMonthInd: varMonthInd  +24 })

    )

)

[madlad · Accepted solution]

One thing you could do is: 

Since you have every month/year combination listed in colMonthYear2, and have row numbers up to 36,

you could lookup the row with they year and month you have chosen, and get the row number for that.

In both combobox's OnChange, try something like:

Set(VarMonthInd, LookUp(ColMonthYear2, Year = YOURYEARCOMBOBOX.Selected.Value && Month = YOURMONTHCOMBOBOX.Selected.Value).RowNumber)

Hope this helps!

[Calin_Mateut]

It works! I did changes in month combobox. Thanks @madlad !

On Change:

Set(varMonthInd, LookUp(ColMonthYear2,Month=MonthDropdown.Selected.Value, RowNumber));

On Set:

UpdateContext({varMonthInd:LookUp(ColMonthYear2,Month=MonthDropdown.Selected.Value && Year=YearsDropdown.Selected.Value,RowNumber )})