1 of 1 people found this helpful
Welcome to the Forum.
Well, I don't think I got there, but maybe the below can give ideas.
There is very likely a more straightforward way to go about this,
particular since you have nicely unioned the data which gives you the benefit of
[Table Name] to work with.
I instead took a roundabout method of many steps:
- creating a data scaffold of all possible dates
- aggregating on the day level the 2017 and 2018 data separately
- joining the 2017 and 2018 data to the scaffold
(I think this is what allows for getting the running 2017 % and applying it to 2018)
- calculating the running 2017 %
- calculating the max 2018 date
- calculating the value at the max 2018 date
- calculating the projected quarter value
- show either the true 2018 value or the projected value
I also made an attempt to have it restart every quarter, but will need more data to check that.
I can describe these steps in further detail, if needed.
First just wanted to see if this was in the ballpark for getting the right numbers.
Then wanted to see if this approach is feasible to you, depending on the size and type of your dataset.
Please see the workbook v10.3, flow, and scaffold attached in the Forum Thread:
Thanks - based on your summary so far, I think I can get to the end, but let me take a look at the stuff you attached. I appreciate tremendously the work you've put into this! Thank you so much! I'll update this thread once I make progress.
I worked on this last night and it works like a charm. I love the relatively simple approach, so even though there are a few steps, they're easy to understand. Thank you again for your help.
Thanks for the follow-up, I'm glad that was helpful.
If I may so request, I would be grateful for your advice regarding this union problem.
The question in summary: 2017 data has been unioned to 2018 data.
For those dates in 2018 that haven't occurred yet, need to generate a projected number
based on what happened up to that same date in 2017 (running % of a quarter's total).
I tried using the [Table Name] field, but got tangled in the table calculations.
So I went a different longer route, but it felt that there should be a more straightforward way
using the [Table Name] field.
3 of 3 people found this helpful
Don't have time to analyze your answer.
This is starting from scratch.
This includes complicated table calc and only one step miss makes results completely different.
Make it sure you follow the EXACTLY same steps.
1 of 2 people found this helpful
Thank you very much for your time, your solution, and your detailed and clear explanation.
I have bookmarked this thread for future reference.
Your approach is much better because it doesn't require a date-scaffold or Prep.
I thought that it was set up nicely for your Union brilliance, but I see now that I
had many more steps to go.
Thank you again.
You are welcome.
Just wanted to add my thanks to you for this great reply. As I get more comfortable with these calculations, this thread is going to be like a reference point for my future work.
Happy Holidays to everyone reading this :-).