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

PrevContributor

  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"

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

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