Calculated formula

How can I create a column with a calculated formula that calculates the second largest value in a range of columns that vary according to the period (at least 7 columns)?

A little more detail preferably with an example would be helpful. Are you actually performing a calculation or simply trying to find the second largest existing value amongst existing data in your range of columns? Also what does â€śvary according to the periodâ€ť mean?

I am trying to finde the second largest value among an existing data. Each column is a date, for exemple: week 1, week 2, week 3 â€¦ week 40.
I would like to create a new column to bring me the second largest value from the week 39 to 32, if there are not at least 7 values, the formula should bring me the second largest value from the week 38 to 31, if there are not at least 7 values the formula must go to another range of weeeks.

If there are at least seven values, i.e. non-blanks in a group of weeks are you done? Could you upload some example data?

formula example.xlsx (9.6 KB)

Yes, if thare at least seven values I am done. See examples attached.

There are no column headers. Where are the weeks youâ€™re referring to? Are you checking separately for each store or simply by column? If the latter, by eyeball your dataset wonâ€™t satisfy what you want.

1 Like

Hello, see attached to file with column headers updated. It is a rude database with a lot of stores and products. Nowadays I use Excel to calculate using the formula (in the yellow cell).
formula example.xlsx (9.7 KB)

Your Excel formula doesnâ€™t appear to do what youâ€™ve described in this post. It doesnâ€™t have a sliding 7 week window. It just keeps adding weeks. Please explain in detail what youâ€™re doing.

I think I got an idea of what you are trying to do. Please see the attached workflow:

What I did is the following: for every combination store-product, see if there is at least seven non null values.

• If yes, take the first seven ones and take the second biggest value.
• If no, compute the maximum of the values.

Hope it helps. Next time you make a post on the forum, remember to attach data and explain your problem in detail: we are complete strangers and do not know what you are trying to do! We cannot help if we cannot get the picture

Have a nice evening,
Raffaello Barri

2 Likes

Hi @lelloba @rfeigel thank so much for your help. It is the first time I am trying to use this kind of formula using Knime, that is why was difficult to explain. Now I could understand better the flow using variables and it will help me for another projects. Thank you!

1 Like