Daily forecast spread using quarterly forecast total

[smanda]

Has anyone leveraged Alteryx to spread the quarterly forecast counts to daily counts using prior year same quarter actuals as anchor/baseline ?
Is it doable ? I have the methodology but need specifics to transition from excel to alteryx

[Yoshiro_Fujimori]

Hi @smanda ,

Could you give us a specific example of input data and expected output data, which you are doing on Excel now?

[Yoshiro_Fujimori]

To answer your question "How can I sent my data ?When I attach excel it says format not supported" sent as a private message,

You should be able to attach .xlsx file via the "browse" link below.

It does not need to be the whole data, but we need just enough data to exactly understand what you mean.

If it's not too big, you can simply copy the Excel range and paste it on your post (like below).

Input Data

Quarter	Count
2023Q1	360
2023Q2	180
2023Q3	540
2023Q4	360

Expected Output

Date	Count
2024-01-01	2
2024-01-02	2
2024-01-03	2
2024-01-04	2
2024-01-05	2
2024-01-06	2
2024-01-07	2

[smanda]

Thank you
Please see attached.

[Yoshiro_Fujimori]

Hi @smanda ,

Thanks for the data.

Looking at the sheet, I have a few comments.

[Prior Year Day] column is inconsistent to [Prior Year Date]

Column D has date value "2022/04-04"" while column B has "2023-04-04", which makes the Week of Day wongly displayed.

The corresponding date of This Year is not on the same row as Prior Year.

As Alteryx deals with each row, it is recommended to have the relevant information in the same row (like the sample workflow below).

If you want to get This Yr Date with the same Day of Week of Prior Yr Date, you may use this function in Formula Tool.

This Yr Date = DateTimeAdd([Prior Yr Date], 52 * 7, "day")

Prior Yr Date	Prior Year Counts	This Yr Date	Day of Week
2023/4/4	31	2024/4/2	Tue
2023/4/5	54	2024/4/3	Wed
2023/4/6	88	2024/4/4	Thu
2023/4/7	106	2024/4/5	Fri
2023/4/8	136	2024/4/6	Sat

You may also want to copy the same values to each row which you use in the calculation.

This Year Counts =

Round([This Year Total forecasted] 

* [Prior Year Counts] 

/ [Prior Year Total Count] 

* [Prior_Yr] 

/ [This_Yr], 1)

Output

Prior Yr Date	Prior Year Counts	This Yr Date	Day of Week	Prior Year Total Count	This Year Total forecasted	Prior_Yr	This_Yr	This Year Counts
2023/4/4	31	2024/4/2	Tue	11006	7594.14	95	94	22
2023/4/5	54	2024/4/3	Wed	11006	7594.14	95	94	38
2023/4/6	88	2024/4/4	Thu	11006	7594.14	95	94	61
2023/4/7	106	2024/4/5	Fri	11006	7594.14	95	94	74
2023/4/8	136	2024/4/6	Sat	11006	7594.14	95	94	95

Once you cleanse the table thus fur, you may add any rules you want. (I would not dive into more details.)

Good luck.

[smanda]

Thank you !
I had the workflow created with formula but unfortunately this does not resolve the  the following that I needed help with: 
1. Match 1st few days of PY and TY regardless of 'Day' of the week until hits1st weekend days
2. Match weekends to weekends
3. Match weekdays to weekdays after #2 and #3 achieved
4. If Prior yr holiday then lookup count for same day in prior week  in the formula