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

PrevContributor

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

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]

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

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