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

Best Answers

  • 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
  • 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
  • The syntax would be [SELECT: TIME.All Periods]
  • [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 monthDAYSINMONTH(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]

Answers

  • 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
  • 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 Periodsdoesn't help
    I also don't need to cumulate values
  • 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