Identify last non 0 value

Nathan
Nathan Active Partner
Board Developer First Comment Level 200: Building A Planning Solution in Board

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,

Accepted Answers

  • Nathan
    Nathan Active Partner
    Board Developer First Comment Level 200: Building A Planning Solution in Board
    Answer ✓

    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
    Helmut Heimann Employee, Community Captain
    500 Comments 250 Likes Fourth Anniversary 25 Answers
    Answer ✓

    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
    Helmut Heimann Employee, Community Captain
    500 Comments 250 Likes Fourth Anniversary 25 Answers
    Answer ✓

    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

Answers

  • ANKITA SAHA
    ANKITA SAHA Active Partner, Community Captain
    Second Anniversary Board Developer 5 Likes First Answer

    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
    Nathan Active Partner
    Board Developer First Comment Level 200: Building A Planning Solution in Board

    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
    Helmut Heimann Employee, Community Captain
    500 Comments 250 Likes Fourth Anniversary 25 Answers

    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.

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

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

    Best,
    Helmut

  • Nathan
    Nathan Active Partner
    Board Developer First Comment Level 200: Building A Planning Solution in Board

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