Formula - last input

CommunityMember113484
CommunityMember113484
in Modeling

Hi

I have a module where teams input information on a quarterly basis.  I then use this to pull through into a summary and compare against previous quarters etc.

However, some teams only input on an annual or half-yearly basis.  In my summary, is there a way to pull through the last time teams input? 

Thanks in advance,

Tagged:

Answers

  • Axel_H
    Axel_H

    Hi,

    There are several ways to perform this.

    You can for example create a time period formatted and time dimensioned line item which returns the time period if a comment is entered.

    If you use the lastnonblank summary method for this line item and have All Periods activated in your time settings, you can retrieve the last period when a comment is entered.

    Use this to lookup the last entered info.

    I hope this helps!

  • CommunityMember113484
    CommunityMember113484
    Hi, thanks for getting back. Do you mind explaining this a bit further? I think I get it conceptually, but in terms of the formulas, not quite there! I've got a new module which is time period formatted (quarter) for last input. But I'm struggling with the last nonblank formula - how do I get the period in which someone has input data?
  • abhi1017
    abhi1017

    Hi @CommunityMember113484 

    Adding/reiterating on to @Axel_H  suggestion.

    Assuming the input module is dimensioned by Quarters/months/weeks.

     

    1. Activate All periods from time settings

    2. Refer  the input line item in the line item created in new module.

    3. Go to blueprint view of the new module.

    4. Scroll to right in blueprint view and In the summary method select "Last non blank" for the line item in which that data is being summarised.

     

    ~Abhi 

     

  • CommunityMember113484
    CommunityMember113484
    Hi, thanks for your input. Sorry, I'm still a bit confused. So I actually tried in the original reporting module to change all summaries to the last non-blank but it didn't work with certain line items e.g. when datatype is NUMBER?

    In terms of the module I created to find the last input, I was able to set the summary to last non-blank and this was able to get me the last nonblank item, but it didn't quite tell me which period was the last non-blank to do the lookup on?
  • Anilkumarch
    Anilkumarch

    Hi @CommunityMember113484 change the number to text format in different line item and use the last-non-blank. Once you are able to find the last-non-blank then write the condition to the period saying if the value in last-non-blank is equal to any period value then get the period value.

     

    In this way to can find the last-non-blank as well as period of the last-non-blank value.

     

     

  • CommunityMember113484
    CommunityMember113484
    Hi - thanks for your help. I've managed to get the last non blank in text format. But in terms of this - Once you are able to find the last-non-blank then write the condition to the period saying if the value in last-non-blank is equal to any period value then get the period value. - do you mind stepping through how to do this?
    Thanks
  • Anilkumarch
    Anilkumarch

    Hi @CommunityMember113484 Create a new line item and write a formula "IF Text Format Item(line item) = Last non blank item(line item) THEN get period value. ELSE Blank

     

     

Categories