Calculated formula

KNIME Analytics Platform
1

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)?

2

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?

3

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.

4

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?

5

formula example.xlsx (9.6 KB)

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

6

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
7

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)

8

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.

9

Hi @felipemelnik ,

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 :slight_smile:

Have a nice evening,
Raffaello Barri

2 Likes
10

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