Previous String Variable input (Top second most string value)

[Aarya_Kankipati]

I have created two variables:

V_Year_week = Year_week ---------> (The variable directs to the field)

V_Max_year_week = Maxstring(Year_week) -------> (Maximum value in that field)

I can't use Max in variable because my field is not a number. I can only use Maxstring.

Note: I need to use variable instead of field name

Year_Week	Product
2024-02	56
2024-01	26
2023-52	58
2023-51	79
2023-50	47
2023-49	44

Requirement A (Previous string)

If user selects, 2023-51 in the variable input dropdown box, The table should show sum of 47 which is 2023-50 data.
If user selects, 2023-50 in the variable input dropdown box, The table should show sum of 44 which is 2023-49 data.

Requirement B
I wanted to use the variable in the sheet. I wanted to filter past 3 weeks.

If user selects, 2023-52 in the variable input dropdown box, The table should show sum of 79 +47 + 44 which is sum of past 3 weeks (2023-51,2023-50,2023-49).

Thank you so much for your time and consideration.

[Aarya_Kankipati]

I have created two variables:

V_Year_week = Year_week ---------> (The variable directs to the field)

V_Max_year_week = Maxstring(Year_week) -------> (Maximum value in that field)

I can't use Max in variable because my field is not a number. I can only use Maxstring.

Note: I need to use variable instead of field name

Year_Week	Product
2024-02	56
2024-01	26
2023-52	58
2023-51	79
2023-50	47
2023-49	44

Requirement A (Previous string)

If user selects, 2023-51 in the variable input dropdown box, The table should show sum of 47 which is 2023-50 data.
If user selects, 2023-50 in the variable input dropdown box, The table should show sum of 44 which is 2023-49 data.

Requirement B
I wanted to use the variable in the sheet. I wanted to filter past 3 weeks.

If user selects, 2023-52 in the variable input dropdown box, The table should show sum of 79 +47 + 44 which is sum of past 3 weeks (2023-51,2023-50,2023-49).

Thank you so much for your time and consideration.

[anat · Accepted solution]

for requirement A:

sum ({$ <YW={"$(=max(YW)-1)"}>} product)

for requirement B:

sum ({$ <YW={">=$(=max(YW)-3)"}>} product)

[Aarya_Kankipati]

Year_week has value like 2023-52, 2023-51.... Year_week is a string..


I Can't use max function for a string I guess

[anat · Accepted solution]

You can try with maxstring function if not works use addweek