Formula for Time Period of Last Input

CommunityMember113484
Occasional Contributor

Formula for Time Period of Last Input

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rob_marshall
Moderator

Re: Formula for Time Period of Last Input

@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

View solution in original post

6 REPLIES 6
rob_marshall
Moderator

Re: Formula for Time Period of Last Input

@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

View solution in original post

CommunityMember113484
Occasional Contributor

Re: Formula for Time Period of Last Input

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
Moderator

Re: Formula for Time Period of Last Input

@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

CommunityMember113484
Occasional Contributor

Re: Formula for Time Period of Last Input

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
Moderator

Re: Formula for Time Period of Last Input

@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

KirillKuznetsov
Certified Master Anaplanner

Re: Formula for Time Period of Last Input

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.