Time Dimensioned Data to No Time Dimension (Last Non Blank)

sonpatel
sonpatel
in Modeling

Hi Community, 

 

I am trying to take data that is dimensioned by two lists and time to a module that is only dimensioned by one of those lists and no time. 

 

Module 1:

           Dimensions: List 1, List 2, Time

           Line Item: Formatted as a month and calculates Start Date and has it show up for the month that it is in (ex: Feb 20 is start date so in Jan 20 the line item will show blank but in Feb 20 the line item will show Feb 20 and in March 20 it will be blank); formatted to be a last non blank summary

 

Module 2: 

           Dimensions: List 1

           Line Item: Is formatted as a month and SHOULD bring in the month that is not blank / is populating in Module 1 but I am not able to get this to populate. 

 

QUESTION: How do I get the line item in Module 2 to populate with the month that is populating in Module 1? 

 

Please let me know if you have any suggestions/ thoughts or if I provide any additional details. Thank you! 

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @sonpatel 

     

    If you have All Time Periods turned on, that would work just as well and will be just as performant as TimeSum().  Using a Select is usually not recommended, but as @jziemer stated, the All Periods is considered structural data (does not change), so Select is good here.  Plus, I am experiencing an issue with TimeSum(), so at this moment, I would go Julie's way.

     

    Rob

Answers

  • rob_marshall
    rob_marshall

    @sonpatel 

     

    A picture might help clear up questions that people may have.  A couple that I have:

    • List 2 - does it have a Top Member defined?
    • What is the summary on the line item in Module 1, I am assuming it is NONE, but just wanted to make sure
    • In Time, do you have All Periods defined?  If not, no big deal.

     

    If you don't have All Time Periods defined, try using TimeSum() in Module 2...something similar to TimeSum(line item in module 1,-1000,1000, FirstNonBlank).

     

    Hope this helps,

     

    Rob

  • jziemer
    jziemer

    @sonpatel 

     

    One solution would be to use [SELECT:TIME.'ALL PERIODS'] in your target formula, pointing to your source line item.  This assumes you have total of all time periods turned on in time setting. Normally, using "Select" is not recommended, but ok if you are referring to time and you have the total of all time periods turned on in time settings. I will think through this a bit more to see if I can come up with more efficient solution.

  • jziemer
    jziemer

    And, as always, Rob's solution is the better solution 😊

  • sonpatel
    sonpatel

    Hi Rob! 

     

    Thank you for taking the time to help me with this. To help answer your questions: 

     

    • List 2 does have a top member defined 
    • The summary of the line item in module 1 is last non blank. 
    • In time we do have all periods defined 

    I ended up using a select and was able to get the result I needed but curious on your thoughts on the pros and cons between the two solutions? 

     

    Thank you!

  • sonpatel
    sonpatel
    Hi @jziemer!

    Thank you for taking the time to help me with this! Is there a reason why you believe Rob's solution to be more efficient than using a select? Is it solely on the basis that using a select is not recommended?

    Thank you!

Categories