# Last non empty value

Hi all,

does anyone has a solutuion for the following scenario?

In my calculated row I want to show the last non empty value of the columns A and B.

For A the result would be 0,01% and for B 0,01%.

BR / Marlene

0

## Comments

Niels ThomsenHi Marlene

First of all - is it correct to understand that you consider 0,00 % to be an empty result?

Also - in my example, I'm assuming you only have one measure in play.

If that is the case, I think we can solve this by creating a calculation as a new measure, that finds the last value in each column and transfers it to the new measure (m2).

if sum(0, 1:d-1, m1) = 0 and sum(0, 0:d-1, m1) > 0 then sum(0, 0, m1) else 0Explanation:

if we have reached a row where the sum from the next row (1) to the bottom row (d-1) = 0 AND the sum from the current row (0) to the bottom row (d-1) gives a result that is greater than zero, then we will have reached the last value that is different from 0 in that particular column.

Now we have the last non-empty value in each column isolated in a measure (m2).

Then we can make a calculated row with this syntax:

sum(0, all, m2)Finally we hide the calculated measure and we are done.

Marlene KnaupHi Niels, thank you for your help.

In this case, 0.0% is actually an empty result ;)

My table shows two different measures.

I build this example in TARGIT Online Demo data:

In this case I want to see -78,1 and 2.556.000 in my calculated row.

Niels Thomsenok - great, I think the solution just needs a few tweaks.

1. We have this table (scrolled to the bottom)

Now we have to make 2 intermediate calculations (1 for each measure).

First intermediate calculation (as a new measure) called Last non-empty Budget Variance % with the syntax:

if sum(0, 1:d-1, m1) = 0 and sum(0, 0:d-1, m1) <> 0 then sum(0, 0, m1) else 0Note: this refers to m1 (which is Budget Variance % - and also note that is tests for <> 0 to make sure we capture negative values

This will create a new measure m3 like this - note that the last non-empty value is the only value for this measure.

Now we make another intermediate with the syntax changed to m2 (to test for Budget Amount).

if sum(0, 1:d-1, m2) = 0 and sum(0, 0:d-1, m2) <> 0 then sum(0, 0, m2) else 0Now our table look like shown below (this calculation is now m4 and will the last non-empty value is the only one for this measure)

Now we make a row calculation where we need to identify which measure we are looking at to figure out if we need to sum up m3 or m4.

if allcount(d1, d1, m1:0) = 1 then sum(0, all, m3) else sum(0, all, m4)Explanation: the first allcount actaully counts its way through the measures. As long as we are looking at m1 the count will be 1, but when we get to m2 - the count will be 2.

Now we just hide our intermediate measures - and our final table looks like this:

Marlene KnaupHallo Niels, thank you very much! This works fine, but having the last row <> 0, the result has still a little problem:

Niels ThomsenYou're right - I changed the 2 intermediate measures a little - so now the syntax is:

if (sum(0, 1:d-1, m1) = 0 or sum(0, 1, m1, 99999) = 99999) and sum(0, 0:d-1, m1) <> 0 then sum(0, 0, m1) else 0Note that the

ORcondition is a test if we have reached the last row.If

sum(0, 1, m1, 99999)returns 99999, it's because the 4th parameter has been activated (which it does when you try to reference out of scope.The other intermediate is the same (just for m2).

br Niels

Marlene KnaupIt's working! Thank you for this little Christmas gift ;)

Niels ThomsenGreat to hear :-)

Please sign in to leave a comment.