
1. Re: VLookup Type Calculation
swaroop.gantela Nov 21, 2018 6:47 PM (in response to Manas Datta)Manas,
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:

2887993scaffold.tflx 27.9 KB

287993scaff.xlsx 24.0 KB

287993scaffold.twbx 80.9 KB


2. Re: VLookup Type Calculation
Manas Datta Nov 22, 2018 6:49 AM (in response to swaroop.gantela)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.

3. Re: VLookup Type Calculation
Manas Datta Nov 23, 2018 3:48 AM (in response to swaroop.gantela)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.

4. Re: VLookup Type Calculation
swaroop.gantela Nov 26, 2018 3:22 PM (in response to Manas Datta)Manas,
Thanks for the followup, 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.

5. Re: VLookup Type Calculation
Shinichiro Murakami Nov 26, 2018 6:59 PM (in response to swaroop.gantela)3 of 3 people found this helpfulDon'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.
Thanks,
Shin

6. Re: VLookup Type Calculation
swaroop.gantela Nov 26, 2018 7:56 PM (in response to Shinichiro Murakami)1 of 2 people found this helpfulShinichiro,
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 datescaffold 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.

7. Re: VLookup Type Calculation
Shinichiro Murakami Nov 26, 2018 9:39 PM (in response to swaroop.gantela)You are welcome.
Shin

8. Re: VLookup Type Calculation
Manas Datta Dec 13, 2018 1:14 AM (in response to Shinichiro Murakami)Shin,
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 :).
Manas