Identify last non 0 value

Nathan · 2024-10-25T10:20:58+00:00

There was an error rendering this rich post.

Hi,

I have a cube of 2 dimensions,

Month
Entity 1

In a procedure, I would like to identify the month with the last non zero value. I tried with the time functions as well as the analytical functions. I may have gone through those features a bit quickly and probably missed the solution.

Would you have any idea how to do it in board ?

Thank you very much,

Regards,

Find more posts tagged with

Accepted answers

Nathan

Hello Helmut,

Thank you for your answer. I uploaded a screenshot (below) to express my case clearer. So, I would like to identify the last non zero period, highlighted in blue to add a fixed value stocked in a cube with the account dimension. Considering I have 400+ accounts, I would like to find a quick way to pin pout those last non zero values.

Thank you very much.

Helmut Heimann

Hi @Nathan ,

the loop will provide a cube by month/account with a "1" in the last non-zero month per account.
You can then multiply this cube by the "stocked value" which leaves you with the additional % on the last non-zero month per account (first dataflow). This cube then has to be added to the original - it will just add up the "stocked value" to the values already in the last non-zero months (second dataflow).

Best,
Helmut

Helmut Heimann

Hi @Nathan ,

you would just loop across the months, the accounts will be taken into consideration in the appropriate dataflows. So, it could look like this

Before:

After:

One thing, you might come across, is that you need to check whether an account has already been "done" regarding its last non-zero month.

Best,
Helmut

All comments

ANKITA SAHA

Hi,

Lets assume we have a dataview like this.

The structure of the layout –

The procedure –

Explanation –

I am checking whether there is value in current month & whether there is value in the next month(using period offset).

If there is value in current month and no value in next month then it is the last non-zero value.

Hope the solution helps!!!

Nathan

Hello,

Thank you for your answer. Would you have another solution without using the period offest ? Indeed, I may have cases where I have several months between 2 values. Like, value in january and novembre but nothing in between.

Thank you,

Helmut Heimann

Hi @Nathan ,

you'll probably need a loop across the months starting from the highest (e.g. if the time range covers Jan.24 to Mar.25, you'd need to start at Mar.25), checking for a value on the subsequent months into the past. The first in this sequence with a value will be your last month with a non-zero value.

Extract the current time range with a descending sort applied to the month
Import the extracted sort order
select the month according to the order
check value, set mark

You can have a deeper look into the configuration of loops in Board here in our How-To section:

https://community.board.com/discussion/18090/how-to-loop-procedures

Best,
Helmut

Nathan

Hello Helmut,

Thank you very much.

Helmut Heimann

Hi @Nathan ,

Helmut Heimann

Hi @Nathan ,

you would just loop across the months, the accounts will be taken into consideration in the appropriate dataflows. So, it could look like this

Before:

After:

One thing, you might come across, is that you need to check whether an account has already been "done" regarding its last non-zero month.

Best,
Helmut

Nathan

Thank you very much. You answered perfectly my case. I set up the procedures into my client's application.