How to lookup a value from a source with no time

[Chmacpherson]

Context

1. My Destination module has the following dimensions: (1) Employee ID (2) Line Items (3) Time
2. My Source module has the following dimensions: (1) Movement Code (2) Line Items
   • Employee ID is a line item (List)
   • Effective Date is a line item (Date)

Goal

1. In the destination module, populate an employee's job level on the effective date based on the Source module.

Challenge

1. Source module has the effective date as a line item and not a dimension, so I am struggling to make the connection between the source and the destination.
   • Destination module (time as dimension)
   • Source module (time as line item)

Any guidance on this would be greatly appreciated!

Many thanks,

Chad

[ManjunathKN]

Hi 

you need to have at least one common dimension in source and target to achieve this,

If your source module has time in line item and your target module has time as dimension.

Then in your target module use the formula

if Item(time)=Time line item in source then your data from source[lookup: on your common dimension]

Thanks,
Manjunath

[Chmacpherson]

Thank you for your help trying to solve this! I am not sure why the above did not work, but I believe the Item(Time) portion wasnt working because the Destination/Target module had the time scale set to day.

All the best,
Chad

[ManjunathKN]

I got your question wrong. My solution will work if you have month as time scale in both source and target.

Thanks,

Manjunath

[Chmacpherson]

Ah no that is my bad. I did not mention not (I did not realize its importance. Thank you again!

[ManjunathKN]

Hi

In the challenge you said, you have to bring the data from non time module to time dimensioned module.

In your context you are telling that your source module has date in line item and your want to bring into time as dimension in your target module. Does it mean that you want to add all the value of dates in that week or month, to be added in your target module, Because Anaplan allows only week/months/quarters/years as Calendar.

In source module you used have Movement code as dimension, is it a concatenated list? can you show some dummy values Screenshot.

Thanks,
Manjunath

[TonyViGrand · Accepted solution]

Hi @Chmacpherson 

I assume the timescale in the destination module is "Day", if that is the case then you can try the formula as  
Data from source[Firstnonblank:Employee ID,firstnonblank:EffectiveDate] .

If the timescale is month in destination, then first create a line item example "EffectiveMonth" in source module to convert the date into month using the Period formula and then use the above formula with instead of EffectiveDate line item in firstnonblank, you could use EffectiveMonth line item.

Thanks

Tony

[Chmacpherson]

Thank you for your help solving this!

You were correct that the destination/target module which has the time dimension has the time scale set to day. I used the FIRSTNONBLANK formula just as prescribed and that worked like a charm.

Thank you again for the help and the quick reply!

All the best,

Chad