
1. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 6:35 AM (in response to Haan Haan)Hi, Haan
Please find my solution attached. below is the screenshot of result for your reference.
Hope this helps
ZZ

Book6_v10.2.twbx 109.8 KB


2. Re: Get differences of a calculation
Haan Haan Jul 23, 2018 6:58 PM (in response to Zhouyi Zhang) 
3. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 7:15 PM (in response to Haan Haan)Hi, Haan
There is a little bit different logic wise.
my understanding of the nearest day is the min date gap for the whole branch, if the search date is 15 Jan 2018, my calculation will return the nearest as 16 Jan 2018 while the one you listed will return 08 Jan 2018.
So not sure which is your desired date, but you can replace mine with yours in case your expectation is the nearest of any date earlier than the search date.
ZZ

4. Re: Get differences of a calculation
Haan Haan Jul 23, 2018 7:31 PM (in response to Zhouyi Zhang)Dear ZZ
Ya, my desired date is the nearest of any date earlier than the search date. Could you please teach me how to do it?
I am not sure how to replace...

5. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 8:38 PM (in response to Haan Haan)Hi, Haan
In my solution, I think by removing ABS from below two calculation fields should work the way you expected.
I attached the updated workbook for your reference.
ZZ

Book6_v10.2 (2).twbx 108.6 KB


6. Re: Get differences of a calculation
Haan Haan Jul 23, 2018 8:46 PM (in response to Zhouyi Zhang)Dear ZZ
I realized that we cant bend the "date" or else the calculation will be malfunction.
May I know why?

7. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 8:54 PM (in response to Haan Haan)Hi, Haan
Sorry I didn't understand what's your question, can you show a screenshot of the issue?
ZZ

8. Re: Get differences of a calculation
Haan Haan Jul 23, 2018 8:55 PM (in response to Zhouyi Zhang) 
9. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 9:29 PM (in response to Haan Haan)Hi, Haan
Can I ask why you need link the date for both data sources?
ZZ

10. Re: Get differences of a calculation
Haan Haan Jul 23, 2018 9:47 PM (in response to Zhouyi Zhang)Dear ZZ
Because in my another project (using same approach), their similarity is the date.
One data source is the total of each day and another is break down of the total.
If i do not link them, the total from the first data source will be duplicate...
Do u get me?
You may refer to this link

11. Re: Get differences of a calculation
Zhouyi Zhang Jul 23, 2018 10:11 PM (in response to Haan Haan)Thanks for your explain.
In this case, can you use avg instead of sum for your totals?
Is it the reason why you use [Amount]/2 in your sample workbook?
ZZ

12. Re: Get differences of a calculation
Haan Haan Jul 24, 2018 12:18 AM (in response to Zhouyi Zhang)Dear ZZ
Nope..the reason of /2 is part of the calculation.
Forget about that...
Refer to the attached workbook, i have changed the "calculation 3"
And am facing the same issue..
May I know how to do/ modify it with same criteria ( the nearest of any date earlier than the search date)?

Book6.twbx 86.4 KB


13. Re: Get differences of a calculation
Zhouyi Zhang Jul 24, 2018 5:07 AM (in response to Haan Haan)Hi, Haan
I am bit of confused of your requirement. but I try my best to explain my thoughts as below
Please confirm whether the logic for calculation is correct or not.
The workbook also attached.
Let me know if any question.
ZZ

Book6 (1)_v10.2.twbx 112.8 KB


14. Re: Get differences of a calculation
Haan Haan Jul 24, 2018 9:43 AM (in response to Zhouyi Zhang)Dear ZZ
Calculation 3 is (searched amount/ total value from 2nd data source)
Nearest Calculation 3 is ( the nearest of any date earlier than the search date for
searched amount/ total value from 2nd data source)
Diff is (Calculation 3Nearest Calculation 3)
For eg search date is 8/1/2018 (expected result)
Calculation 3  21.15%
Nearest Calculation 3  30.95%
Diff  (21.15%30.95%)/30.95%=0.3167%
Thanks