How to substract last month from this month using procedure

川上 まりえ
川上 まりえ Active Partner
Second Anniversary Board Developer Level 100: Foundations of Building in Board Level 200: Building A Planning Solution in Board
edited June 2022 in Platform
Hi there,

We use Board 12.3.
I want to know how to calculate the total of product B.
Please see the table below. I want to know how to create cube and use procedure to calculate this result.
Jan.22 Feb.22 Mar.22 Apr.22
productA 999 b.200 100 100
productB a.500 c.300 200 100
(c)300=(a)500-(b)200

This cube has Date types.

Thanks for your help.

------------------------------
?? ???
??
MRI VALUE CONSULTING AND SOLUTIONS CO., LTD.
Japan
------------------------------

Answers

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    50 Answers 500 Comments 250 Likes Fourth Anniversary
    edited June 2022
    Hi ?? ??? , as well (sorry, but I can't interpret your name)

    this looks like the period-by-period recursion might be the right thing for you.
    Please, have a look at our help page here: Dataflow (boardmanual.com)

    Kind regards
    Helmut

    ------------------------------
    Helmut Heimann
    Senior Consultant
    Board Community
    Germany
    ------------------------------
    -------------------------------------------
  • 川上 まりえ
    川上 まりえ Active Partner
    Second Anniversary Board Developer Level 100: Foundations of Building in Board Level 200: Building A Planning Solution in Board
    edited June 2022
    Hi Helmut,

    Thanks for your reply.

    By using period-by-period recursion, we can calculate the value of the previous month like loop?
    If so, I want to know how to use it. I gave it a try, but It didn't go well.
    image
    Kind regards
    Marie

    ------------------------------
    ?? ???
    MRI VALUE CONSULTING AND SOLUTIONS CO., LTD.
    Japan
    ------------------------------
    -------------------------------------------
  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    50 Answers 500 Comments 250 Likes Fourth Anniversary
    edited July 2022
    Hi Marie,

    the period-by-period recursion will take the value of the previous period to calculate the value of the actual period.
    This will help you avoid using loops on time dimensions (which you could, of course, still use).
    Both blocks in the dataflow need to be the same, the source needs to have a negative period offset (in your case, one month into the past, assuming that your cubes' time dimension is "month"). That's the condition for using the period-by-period recursion, in the first place.

    When you say, you've tried that and it didn't go well -- what were the results in your case?
    But, after looking at your example once again, I've seen what's troubling you. You need to shift values from one product to another over time -- that's a bit more tricky. 
    So, period-by-period recursion will perhaps (if it does at all) help in one of the dataflows you'll have to build up to achieve your aim.
    What's the rule for productA in Feb22 to be 2/5th of productB in Jan22; and the rule for March's value to be derived from February's value is not the same, is it?

    Kind regards
    Helmut

    ------------------------------
    Helmut Heimann
    Senior Consultant
    Board Community
    Germany
    ------------------------------
    -------------------------------------------
  • 川上 まりえ
    川上 まりえ Active Partner
    Second Anniversary Board Developer Level 100: Foundations of Building in Board Level 200: Building A Planning Solution in Board
    edited July 2022
    Hi Helmut,

    I'm sorry I didn't make it clear enough. 
    The sample wasn't very good.

    Actually, we want to prepare a balance sheet using actual results data.
    In the end, we suppose the below results.

    Balance of this month(C) = Balance at the end of the previous month(A) -  the amount rises and falls in this month(B) 

    In this case, we use the result of (C) for the next month calculation, do the same again the next month...It is an understanding like loop.
    imageimage
    If you have any questions, please feel free to let me know.

    Kind regards
    Marie

    ------------------------------
    ?? ???
    MRI VALUE CONSULTING AND SOLUTIONS CO., LTD.
    Japan
    ------------------------------
    -------------------------------------------

Categories