Automatically fill missing values for specific month

[DanS_AMS]

Dear community,

I am relatively new to Anaplan but the answers to questions from other members in this community already helped me alot. On my current question I could not yet find a good answer, so I post my first request as follows:

Scenario: On the first day of each month, I use the data for several KPIs (KPI1 and KPI2) for three countries (Country1, Country2, Country3) from a source. However, the data arrives with a delay of 1 day (for Country 1 and Country2) and two days delayed for Country3. 

Problem: I would like to fill the missing data with the average value of this KPI and region for the respective weekday. I.e. the missing data for 31-Jan (Sunday) for Country2 and KPI1 should be filled with the average value from KPI1 and Country2 of prior sundays in January.

Ideally, the workflow should detect which days/data in a specific month (here January 2021) are missing, since this can vary from time to time.

Please find attached the sample dataset. The expected results would be as follows:

Thanks a lot for your help in advance!

Daniel 

[OllieClarke · Accepted solution]

Hi @DanS_AMS here's my approach:

First I generate all the days in the months that each country and account have data.

Then I use a join to find the missing days. I then use another join to find the values for all the same weekdays in that month. Finally I can average these values.

Hope that helps,

Ollie

[DanS_AMS]

Hi @OllieClarke,

That is exactly what I need! Thank you so much!

The "Generate Rows" tool is what I was missing and I think essential for this approach.

Thanks again!

Best,
Daniel