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

7 comments
  • Hi 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 0


    Explanation: 
    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.

    0
  • Hi 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.

    0
  • ok - 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 0

    Note: 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 0

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

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

    0
  • You'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 0

    Note that the OR condition 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

    0
  • It's working! Thank you for this little Christmas gift ;)

    0
  • Great to hear :-)

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post