Establish a connection between fake time and real time

melliott
melliott
edited September 11 in Modeling

I have a csv upload (see below) with a column for FY and an individual column with the qty value of each month. I cannot change the headers prior to import. To view this data by anaplan native time, I know I need to create a fake time to real time conversion, but I'm not sure where to start, thanks!

image.png

Answers

  • seymatas1
    seymatas1

    You should work with the IT team to reformat the file so that it combines months and years into one column. For example, each row will have "Jan 20," "Feb 20," etc., and you will have an "Amount" column.

    Then set up a module in Anaplan with time (months) as the dimension and "Amount" as a line item. This will make it the most efficient way to import the data into Anaplan.

    Fixing the data format at the source before importing numbers into Anaplan is a more sustainable approach.

    I hope this helps.

    Seyma 🌷🙂

  • melliott
    melliott

    Unfortunately, I cannot alter the import. Long term, we may be able to get that changed, but this format is what I'm working with for now.

  • Preethi
    Preethi

    @melliott

    In your import module create 12 line items for each month & format it as Month Time period & derive the anaplan month for each year using finditem

    image.png

    Next create a target module dimensioned by time & list same as your source module.

    Then you can run the import from source to target for each month separately, total 12 imports starting from Jan to Dec or use a logic in target module to pull in the values using SUM forumla.

    Ex: "IF LEFT(NAME('Item: Time'), 3) = "Jan" THEN 'test'.JAN[SUM: 'test'.Jan Month] ELSE if LEFT(NAME('Item: Time'), 3) = "Feb" then 'test'.Feb[SUM: 'test'.Feb Month] else" & so on until December

    image.png

    Hope this helps.

  • seymatas1
    seymatas1
    edited September 12

    Hi @melliott
    Please do not use 12 nested if statements. It violates the best practices.

    Try to follow this approach.

    1. Create a UID column in the file.
    2. Create a UID list.
    3. Create a fake month list with 12 items.
    4. Create a budget year list with 2 items.
    5. Create a transaction data module with one dimension: UID list. Let's call it DAT01.
    6. DAT01 has line items BudgetYear, Jan, Feb, etc.
    7. Import the file's UID column into the UID list.
    8. Import the data from the file into DAT01.
    9. Create DAT02 module. It has budget year list as a dimension. No native time dimension.
    10. Import numbers from DAT01 to DAT02.
    11. Create a SYS01 Time module with the time dimension and two line items: Budget Year and Month. Use text functions to combine the Budget Year and Month to resemble Anaplan's time format (e.g., Jan 20).
    12. Use FINDITEM to map this to Anaplan's native time.
    13. Create another module: DAT03 with Amount line item, and Anaplan's native time dimension.
    14. Use a LOOKUP formula and SYS01 module to bring numbers from DAT02 to DAT03.
    15. Combine your actions in a process, and publish on a UX page.

    I hope this helps.
    Seyma 🌷🙂

Categories