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

Accepted answers

rob_marshall

@CommunityMember113484

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

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)

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

Result:

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

Hope this helps,

Rob

All comments

rob_marshall

@CommunityMember113484

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

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:

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

Result:

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

Hope this helps,

Rob

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

@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:

Rob

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

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

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

@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

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…