Formula for Time Period of Last Input

CommunityMember113484
CommunityMember113484
in Modeling

Hi,

I have a reporting module that reports actual performance on 12mth, 3yr, 5yr basis.  It is dimensioned by quarters and teams input information in relevant quarters e.g. Q4 2020, Q3 2020

How do I create a summary module that picks up:

1. Latest input quarter - I've tried LASTNONBLANK 

2. 2nd latest input quarter (i.e. comparison quarter). 

Any help is appreciated,
Thanks

 

 

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @CommunityMember113484 

     

    Gibe this a shot...The first trick is to add All Time Periods in your Time settings area:

    2021-03-02_12-33-12.png

    I created three line items with the following formula

    Data: I didn't have a formula, but you can link to another module if needed

    Last Quarter Entered: IF Data <> 0 THEN ITEM(Time) ELSE PREVIOUS(Last Quarter Entered)

    Previous Non Blank: 


    IF Last Quarter Entered = Last Quarter Entered[SELECT: TIME.All Periods] AND ITEM(Time) >= Last Quarter Entered[SELECT: TIME.All Periods] THEN PREVIOUS('Previous Non Blank') ELSE PREVIOUS(Last Quarter Entered)

     

    2021-03-02_12-39-40.png

     


     

    I also changed the summary on the last two to be last nonblank

     

    2021-03-02_12-38-31.png

     

    Result:

    2021-03-02_12-41-08.png

     

     

    Now, in your other modules, you can just get the All Periods value using the Select: Time.'All Periods'

     

    2021-03-02_12-43-56.png 

     

    Hope this helps,

     

    Rob


Answers

  • CommunityMember113484
    CommunityMember113484
    Hi Rob,
    Thanks for the above - this has worked perfectly.
    There's one more step which I forgot to mention, how do I in a separate module / same module then obtain the value input by looking up the period from Last Quarter and Previous Quarter?
    Thank you
  • rob_marshall
    rob_marshall

    @CommunityMember113484 

     

    So, I would do it in a separate module because (and I am assuming here) you will not need the Time Range.  Just do a lookup like the below:

     

    2021-03-02_14-38-35.png

     

    2021-03-02_14-38-45.png

     

    2021-03-02_14-39-11.png

     

     

    Rob

  • KirillKuznetsov
    KirillKuznetsov

    Not sure if you used formula or summary method. So it worth t mention here that you should try to use last non blank method on summary method via blueprint

    KirillKuznetsov_0-1614755240890.png

     

    If i understood correctly you want to have some kind of logic created for summary calculation. In this case Ratio method can help. Please check Anapedia for Ratio.

    You can use "1" as your denominator and create some line item with formula required as a numerator of the ratio.

     

     

     

  • CommunityMember113484
    CommunityMember113484
    Amazing, thank you again.
    One other thing, the model currently picks up data in Q1 FY21 with the last input Q4 FY20, how do i keep future periods as nil?
    Thanks
  • rob_marshall
    rob_marshall

    @CommunityMember113484 

     

    Good question, but you really don't need the future periods as nil/null because all you really. care about is the values in the All Periods slot.  Additionally, the way the logic works, people can "skip" a period and the logic will still get the previous "filled out" quarter.  So, for all intents and purposes, the future periods are not a big deal as you would not be publishing this to an App or dashboard.

     

    Rob

Categories