Formula for Time Period of Last Input

[CommunityMember113484]

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

[rob_marshall · Accepted solution]

@CommunityMember113484

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

我创建了三个行项目使用以下形式ula

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

[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

[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

[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.