How to move from a Model with Timescale to a model without time scale?

Need help in aggregating the values which are available in a model with timescale (and other dimensions) into another module which has all the other dimensions except the timescale dimension. This is required to show the summation or the closing value of the timescale module value at an aggregate level into the new module. I saw a post which shows how to move from a module with no timescale(but a date value) to a module with timescale. I need to do the reverse. Please help. Thanks in advance Saurabh Jain

Accepted answers

PrevContributor

I believe you need to use the SELECT function to tell the model what "item" in the time scale you're referring to. For example, if my line item Revenue has time as a dimension, and my line item TotalYear doesn't, I could set TotalYear equal to Revenue[SELECT: Time.'FY14'] to grab the full year value from fiscal 2014.

https://community.anaplan.com/anapedia/calculation-functions/select

PrevContributor

If you go to the Settings Tab, Time, and look near the bottom there is a checkbox option that enables a summary for "Total of All Periods". If you enable this you should be able to select it across all years. Does that help? Alternatively you can create a line item to "Cumulate" the value and then you can reference that line item to get more granular as to the total of all periods as of a certain date or date range

https://community.anaplan.com/anapedia/calculation-functions/cumulate

PrevContributor

The syntax would be [SELECT: TIME.All Periods]

PrevContributor

[quote] need to analyze managers sales on the base of month_of_work
So I have the module with sales values (with timescale for 3 years)
then I calculate month_of_work (dlist_formatted) for every manager (in module with timescale)
then I try to build module (where instead of timescale I use month_of_work dimension)
So in sum formula I need to use Sales[sum: month_of_work, select: Time.'for all periods')
and seems it doesn't work
[/quote]

I also don't need to cumulate values

Hi Oleg,

Hope the suggestions below help.

I assume the Month_of_work list has Jan - Dec (12 x items), so create a 'Month no' number formatted property in the list (number Jan = 1 , feb =2 etc)

because you cannot seem to create a mapping table from a the 'Time' dimension, you will need to create a lookup for the Month_of_work list

create a new list called 'Year_list' with elements Year 1, Year 2, Year 3... add a property with 'Year no' number eg 2011,2012,2013. Make sure you put a Tope Level Item in the list.

Create a new module Month_Lookup with NO timescale, Month_of_work list , Year_list and line items below

Year = Year_list.Year no
Month = Month_of_work.Month no
Days in month = DAYSINMONTH(Year,Month)
Month of work Date (date formatted) = DATE(Year,month,Days in month)

You could combine all the line items into one formula, but possibly easier to review above if separate

In the target module (with Month_of_work list and Year_list but NO timescale)

Line item formula =
Source_Module.Amount[LOOKUP: Month_Lookup.Month of work Date]

All comments

PrevContributor

I have the case where select with year - doesnt suit
e.g I need to analyze managers sales on the base of month_of_work
So I have the module with sales values (with timescale for 3 years)
then I calculate month_of_work (dlist_formatted) for every manager (in module with timescale)
then I try to build module (where instead of timescale I use month_of_work dimension)
So in sum formula I need to use Sales[sum: month_of_work, select: Time.'for all periods')
and seems it doesn't work

PrevContributor

Hi Tom
I know how to enable "Total of All Periods"
And the only question is on syntax for select formula
Using select: 'Total of All Periods' and select: 'All Periods' doesn't help
I also don't need to cumulate values

PrevContributor

The syntax would be [SELECT: TIME.All Periods]

PrevContributor

Thank you Tom
Formula works fine
but to my regret it doesn't help with my task (due to sum formula error) - think need to find another approach

PrevContributor

I also don't need to cumulate values

Year = Year_list.Year no
Month = Month_of_work.Month no
Days in month = DAYSINMONTH(Year,Month)
Month of work Date (date formatted) = DATE(Year,month,Days in month)

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
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.…