Customer Banners (Ads) - SpiceUp. AX and SpotfireX Disclaimer



If you find this site useful and you want to support, buy me a coffee   to keep this site alive and without ads.

Fill blanks with last known value

This custom expression will fill the blanks with last valid value. Will replace blanks


Last([B]) over (allPrevious([A])) as [C]


If you have only one row with blanks or you don't want to rely the value on any other column then create a calculated column with 'rowid() as [A]' and use the same expression as above. 




3 comments:

Anonymous said...

If I want that the filling process depends of another variable, how can I do it?

Milan said...

Had the same problem, took me quite some time. A post from Sean Riley solved it for me:
https://community.tibco.com/questions/there-way-substitute-null-values-last-non-null-value-column

Essentially you can use the following code:
ValueForMax([A],[B]) OVER (AllPrevious([A]))

Where [A] determines the order & [B] is the variable with the missing values on certain rows of [A].

This code doesn't use a [C] column AND is not depending on the physical order at which you load data in Spotfire, since [A] determines this order.

Fernando said...

Simple and smart solution. Thank you.