Taking a month of a past day if a list format match

Hi I need help with the following I need to take a value from a past date if a line item with a list format matches with another one, i Mean for example as you can see in the attached image. I have time ( in dates format ) and in a colum with a list formatted (DIA), but i need to change the amount and to use an amount that match with a prior date. So for example on Jan 7th the loaded data is 76,659 but i need to use the amount of Jan 2nd, as you can see I should expect to get 94,531 if the column "DAY1" matches with "DIA"

Accepted answers

simon_ritchie

Hi Rodolfo,

The DIA list looks like it has 7 items, 1 for each weekday.
The 7th Jan is a Monday, so are you wanting something that would read backwards up the list from the 7th until it finds a date that is a match to DAY1 (4-WED)?
If the example date was the 14th Jan, would you want it to return the value 80,325 from the 9th Jan, or still the 2nd Jan?

If you are only ever stretching back 7 days I would do something like this:-

Add an Index property to the Weekday list, number formatted with numbers 1 to 7.
Add some more Line Items:

Date Offset (Number Formatted) Formula:[quote]if isblank(DAY1) then
0
else if weekday.index[lookup: DIA]>=weekday.index[lookup: DAY1] then
weekday.index[lookup: DIA]-weekday.index[lookup: DAY1]
else
7 - weekday.index[lookup: DAY1]+ weekday.index[lookup: DIA][/quote]
Lookup Date (Date Formatted) Formula:
Start()-Date Offset

Suma3 Formula would become:
Amount[Lookup: Lookup Date]

I haven't tested it in Anaplan so hopefully not too many syntax errors

Cheers,
Simon

All comments

simon_ritchie

Cheers,
Simon

PrevContributor

Hi Simon thanks a lot for your help,

Well, to be more specific i would like to take tha value(in Amount) from any day but not necessary the value of a week,

for example if my current day is Jan 22th and In "DAY1" I choose 4-WED and in "PROMO2" I choose PICO17, I'd would like to look up for the value in "Amount" and the result that I should get is 94,531 (match with DIA and PROMO)

I hope to be a little more clear and do not make you confused jeje

)

simon_ritchie

Hi Rodolfo,

There isn't a way to search backwards from a specific date, what you can do instead is carry that value forward using the Cumulate function and a subsidiary view.

I think this should work.
Use an extra Line Item with a subsidiary view, I called mine "Cumulate with Sum". Add the Weekday & Promo lists in the "Applies To" for this new Line Item.
The formula for this new line item would be:

CUMULATE(Amount[SUM: DIA, SUM: Promo])

Then the formula in Suma3 would become:

Cumulate with Sum[LOOKUP: 'Day1', LOOKUP: 'Promo2']

*See screenshots attached*

Hope that helps.
Simon

Quick Links

Can you help?

Unanswered questions

ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.
Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…