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

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!

Accepted answers

rob_marshall

@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

All comments

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

@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

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

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

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!

rob_marshall

@sonpatel

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