Mapping Cohorts in the format YYYYMM to Time value Month

I have a module that has 4 line items, 2 of which work on Month which results in the correct values, however 2 work on something called Cohort - so for example 202204 is Apr 22.

See module below, the top 2 result in blanks/zeros and the bottom 2 result in correct values.

So I want somehow to map the Cohorts to the time value Month, is that possible and if so how do I achieve it?

Source Module:

Accepted answers

devrathahuja

@kmht0307 - Exactly. Because you have applied the cohorts dimension to that line item, remove that dimension from that line item and keep all the other dimensions.

Your lookup in the formula is supposed to carry the 202204 value to Apr 2022, which it will correctly once you remove this dimension.

All comments

devrathahuja

@kmht0307 - Hello again :)

Convert the cohort to time period using formulas. Start with the text of cohorts & try using Left/Mid/Right functions to first separate the year and month number from the cohort text to 2 separate line items

Once you have that, you can use the date function to convert the text to a date and then use the period function to convert it to Anaplan time period.

You'd find more information on all these functions and it's syntax on Anapedia.

kmht0307

I have noticed this module in another model, could this be used to convert the cohort to month using some kind of lookup from cohort to Start Period?

devrathahuja

@kmht0307 - The start period is exactly what you need as well. It is the Anaplan time. You can replicate what is done in this model to get your time mapping too.

kmht0307

Brilliant, I will import this module into my model.

Do you know what the formulae would be to use the Start Period?

devrathahuja

@kmht0307 - You should not import this but recreate the formula so that everytime there is a new period you don't have to remember to import it.

You will need to use lookup on the start period if you want to get the data from the cohort to native time and for the other way around, you'd need a reverse mapping so the cohort list member present against native time.

kmht0307

I thought I would need to have this module in my model.

devrathahuja

Yes you would need it but do not just import the start period, check the formula in the other model and recreate it in the model you need it.

kmht0307

Got it, I have this now in my model:

kmht0307

I have replicated the above in the CI Level Check module instead but not sure how to LOOKUP to the Start Period value from the Blank Tariff module.

devrathahuja

@kmht0307 - You have a mapping AT Cohorts FOR Native time. You need the reverse mapping to be able to use lookup. Your mapping needs to exist AT native time dimension FOR cohorts dimension.

Whatever dimension is in your target module needs to be the dimension in the lookup module. Whatever dimension is in the source module needs to be the mapping item.

This link should help you understand it visually-
https://community.anaplan.com/discussion/53085/lookup-sum-and-textlist-cheat-sheet?tab=all

kmht0307

Thank you for the link, I have looked at it but don't really understand how it can be applied to my model.

Struggling a bit on how to use the reverse lookup.

devrathahuja

@kmht0307 - This may help

You need to create the lookup module with the reverse mapping. You can achieve that using firstnonblank function.

Your current lookup module-

What you need-

kmht0307

Thank you, I now have this:

How do I amend this module line item Relevant Tariff CoS now to bring in the values by the TIME setting:

devrathahuja

@kmht0307 - Add the lookup in your formula [select:…., lookup: _____]

kmht0307

Can you be more specific with the syntax as I cannot follow this in Anaplan

devrathahuja

@kmht0307 -

Source-

Target-

I'd strongly suggest you to go through the L2 training by Anaplan once again to understand these concepts better as to when to use what and how to find the right syntax of formulas. It'd greatly help you for future build as well :)

kmht0307

I understand, I am seeing this error message now:

devrathahuja

@kmht0307 - You need to lookup this mapping -

You are trying to LOOK UP the cohort time value and bring it to your native time.

kmht0307

Okay, I have amended this now, but still see blanks where I should have values, so back to where I started.

devrathahuja

@kmht0307 - Check the data at the lowest level of intersection. Right click to drill down and evaluate which part of the formula isn't working

kmht0307

I have done that and found one of the issues which I have now corrected, and I can now see values coming through for Relevant Tariff CoS. The issue now is that the values are the same for each month and it is picking up the total for all months in each case:

devrathahuja

@kmht0307 - I see all cohorts being present now in the module as a list, if you add that you will have all cohorts value summed up to native time. You need a lookup on that dimension instead.

kmht0307

What do you mean?

This is the current formula:

IF SYS Tariff Type.Relevant Tariff? THEN CI Level Check.CoS[SELECT: Tariff Type.Relevant Tariff, LOOKUP: SYS Time Settings by month.Cohort Time] ELSE 0

devrathahuja

Is this being applied to the final line item?^

Can you attach a screenshot of the final line item opened in the subsidiary view & at the lowest levels in each dimension?

kmht0307

This is the screenshot for Handset only, as you can see the values here are the totals for all Cohorts not just 202204 (Apr 22)

devrathahuja