Daily forecast spread using quarterly forecast total
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Use Case Support
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @smanda ,
Could you give us a specific example of input data and expected output data, which you are doing on Excel now?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you
Please see attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
