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
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.
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.
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:
Hallo Niels, thank you very much! This works fine, but having the last row <> 0, the result has still a little problem:
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
It's working! Thank you for this little Christmas gift ;)
Great to hear :-)
Hi TARGIT Community,
I have a pretty similar example. But in my case I need the last non empty value in every row to show the price of an item over time.
This is how my table looks when I include empty values. I need the numbers in red to display the correct chart.
When I exclude the empty values my chart works but the date axis won't be displayed correctly.
To show a correct date axis I includ empty values but then I don't see any value. That's why I want to work with a calculated measure.
Would be great if there is a way to solve this in TARGIT.
BR,
Eric
Hi Eric,
I think you can do it like this. I have enabled 'empty values' and added a calculation (as a new column):
BR / Ole
Hi Ole,
Thank you very much. This works great. Is there a way to get this working with a dimension on the X-Axis as well? When I try to edit this I don't get the right values.
Hi Eric,
I guess your 'Neue Berechnung' has been added 'As a new measure'...
In that case, your calculation should be something like this:
BR / Ole
Hi Ole,
Thanks for this input. I finally made it with this calculation:
Thank you very much for your support.
BR,
Eric
Please sign in to leave a comment.