Selecting the last (most right) value of a list (of several columns) that is not blank

stuggijo
Participant IV

Dear Community,

I am struggling with to following issue:

I would like to get the last value (that is not blank) of a list that is based on different columns.

What I have found is that it is possible to choose the first value of a list (but I need the last one, that is not blank). What I want to achieve is when f.e. [Krs_v_27_J] is blank, I want [Krs_v_26_J] to be selected.

I have tried it out. But it seems like I miss somethingโ€ฆ

In my impression the values are resortet when I substract the List("") of my manual List( [Krs_v_24_J], [Krs_v_25_J], [Krs_v_26_J], [Krs_v_27_J] ) ) so that the wrong value is shown (not the last one of the list that is not blank).
I have also tried with the Sort expression. But that sorts the values based on the content of the rows, when I understand it correctlyโ€ฆ

What do I miss and do you have an idea on that?

**
Any(
Top(
(
List(
[Krs_v_24_J],
[Krs_v_25_J],
[Krs_v_26_J],
[Krs_v_27_J]
)
- list("")
),
1
)
)
**

Thank you very much!

0 11 312
11 REPLIES 11

You may wish to elaborate if the columns are populated in specific sequence such as [Krs_v_24_J], [Krs_v_25_J], [Krs_v_26_J], [Krs_v_27_J] or is it likely that they are populated in random order , say [Krs_v_24_J] and [Krs_v_27_J] are blank but not [Krs_v_25_J], [Krs_v_26_J]

Are there just 4 columns you wish to find the first nonblank or this is just sample and you have more number of columns to look for a blank?

Is creating a list must for some reason or your only intention is to find the first nonblank column out of 4 and non-list based approach will do?

Thank you for the quick reply, Suvrutt.

All in all I have 30 Columns and they are sorted from low to high in the list.
([Krs_v_1_J] all the way up to [Krs_v_30_J]).

I want to compute a relative value of some absolute value to be shown in a chart. The chart has 30 columns, 1 to 30 point).
Herefore I need to make a specific value be devided by the last (most right) value of the list that is not blank. (Otherwise the relative value would return an error, as the absolute value is devided by 0. So if the last (most right) value is blank, the column last (most right should) be chosen that is not blank.

If the last value (pont 30) [Krs_v_30_T] is not blank, it should calculate f.e. (point 2) ( [Krs_v_2_T] - [Krs_v_30_T]) / [Krs_v_30_T].

But if lets say points (28-30) [Krs_v_30_T], [Krs_v_29_T], [Krs_v_28_T] are blank, the next most right column should be chosen ( [Krs_v_27_T] ).

So that the expression would be:
( [Krs_v_2_T] - [Krs_v_27_T] ) / [Krs_v_27_T].

This expression I compute separately in a virtual column, to make it dynamic, let say [VT_Last_Krs].
( [Krs_v_2_T] - [VT_Last_Krs] ) / [VT_Last_Krs].

Thank you much!

Thank you. Based on the latest, updates shared by you I would concur with @perissf that you could reevaluate your table schema. In general performing math across many columns of the same row could be tedious.

BTW are these columns number or decimal type?

Yes. They are decimals.
So you would rather go back to the solution of making a set of absolute values and another set of relative values instead? (I have tried it, but that means, that much more data needs to be loaded into the app)

Thank you. Do you mean by blank as 0 (Zero) as the default value for each of those columns?

I think it could be done with a convoluted expression.

Now that you have shared the elaborate details, I would suggest you may wish to wait for any easier options someone may suggest. You may surely wish to reevaluate your data schema as well.

Edit: Or maybe using spreadsheet formulas.

The values would be pulled from google finance and if there is no data for the date, i could set the spreadsheet formula to โ€œโ€ (blank) or โ€œโ€“โ€.

As I mentioned, it will be a very convoluted expression to construct across 30 columns of a row.

So request you to evaluate previous suggestions.

perissf
Participant V

It looks to me that the table schema you are using is not the most appropriate. Can you elaborate more? What do you want to achieve in business terms?

I would like have a line chart that shows the historical stock courses.

A: in absolute values

3X_4_0_40821a1381ebc4f0cd9ecbd8686759d2fedf9c3c.png

B: in relative values

3X_0_3_0325308b74f5fac8568ebd78bfa88798b60b583f.png

perissf
Participant V

Iโ€™d definitely go with a table schema like this:
Table Stock
Table Historical Price with columns: ID, Stock, Timestamp (or date), Price, Performance.
Column Performance can be either a VC or a normal column whose value is calculated by a workflow, depending on your preferences.
In this way it will be much easier to adapt your app to future changes like for example the number of prices to graph.

Thank you for your input :).
If I understand you correctly, that means that the historical courses would need to calculate within a sync. I have tried that solution as well, but it takes very long time to do, to get historical chart be shown.

Top Labels in this Space