
1. Re: Get Differences btw today and The nearest of any date earlier than today
Zhouyi Zhang Jul 24, 2018 9:53 PM (in response to Haan Haan)Hi, Haan
What's the difference of this question comparing to your previous one?
ZZ

2. Re: Get Differences btw today and The nearest of any date earlier than today
Mahfooj Khan Jul 24, 2018 10:44 PM (in response to Haan Haan)Hi Haan,
I don't think you can achieve this output if you blend your data sources. Because we can't use LOD{} and Table calculation while blending.
So what I did, I extract your both the sources and joined.
here is my finding
One more doubt, How you're evaluating Nearest day from Today?? I'm getting 13000
To evaluate the correct output I've divided this 13000/10000 so that I can get 1.3
Correct me If I'm doing something wrong.
Workbook attached for your reference. Let us know if this works for you. I've also attached the excel of your sources.
Mahfooj

Test.xlsx 12.5 KB

Book6 (1).twbx 17.7 KB


4. Re: Get Differences btw today and The nearest of any date earlier than today
Haan Haan Jul 24, 2018 11:14 PM (in response to Zhouyi Zhang)Dear ZZ
The difference is the requirement of the calculation is different...
I have added selected measure names in the calculation.( underlined)
Today:[ Amount from Name 1(Cheras and PJ)] / [Total from HQ:A]= 0.2506
Nearest day from Today : The nearest of any date earlier than the search date for "Today"
Answer will be 1.3
Differences : (Today Nearest day from Today) / Nearest day from Today
(0.25061.3)/1.3 = 0.8072

6. Re: Get Differences btw today and The nearest of any date earlier than today
Haan Haan Jul 24, 2018 11:38 PM (in response to Mahfooj Khan)Dear Mahfooj
If the search date is 1/5/2018
Today should be 1.3
(which is the answer of the nearest day from today for 1/8/2018)
So the answer for search date is 1/5/2018 will be
Today= 1.3
Nearest day from today= 6500/1000=6.5
Diff = (1.36.5)/6.5=0.8
Do you get me?
Thanks

7. Re: Get Differences btw today and The nearest of any date earlier than today
Zhouyi Zhang Jul 24, 2018 11:51 PM (in response to Haan Haan)
Book6 (1)_v10.2.twbx 34.6 KB


9. Re: Get Differences btw today and The nearest of any date earlier than today
Zhouyi Zhang Jul 25, 2018 12:40 AM (in response to Haan Haan)Hi, Haan
Your nearest date is based on the search date, so when it is 05 Jan 2018, it picks 03 Jan 2018 from PJ and 04 Jan 2018 from Cheras which is 6500+6500.
While it is 04 Jan 2018, it picks up 04 Jan 2018 from PJ and 02 Jan 2018 from Cheras which 6500+2000.
And for search date, when it is 05 Jan 2018, as it exists in both cheras and pj, while when it is 04 Jan 2018, only cheras has matched date, but pj doesn't have a date 04 Jan 2018. that's why you see the difference.
Hope this make sense.
ZZ

10. Re: Get Differences btw today and The nearest of any date earlier than today
Ankit Bansal Jul 25, 2018 12:43 AM (in response to Haan Haan)Please see the attached worksheet.

Book6_v10.5.twbx 23.2 KB


11. Re: Get Differences btw today and The nearest of any date earlier than today
Haan Haan Jul 25, 2018 12:57 AM (in response to Ankit Bansal)Dear Ankit
However, the information on 5 Jan is incorrect.
If the search date is 1/5/2018
Today should be 1.3
(which is the answer of the nearest day from today for 1/8/2018)
So the answer for search date is 1/5/2018 will be
Today= 1.3
Nearest day from today= 6500/1000=6.5
Diff = (1.36.5)/6.5=0.8

12. Re: Get Differences btw today and The nearest of any date earlier than today
Haan Haan Jul 25, 2018 1:01 AM (in response to Zhouyi Zhang)Dear ZZ
If this is the case, how to achieve that?
If the date for Today, nearest date from today, diff is based on demo file.
While if there is no info on the nearest date from today for the branch, will return to 0.
Thanks

13. Re: Get Differences btw today and The nearest of any date earlier than today
Mahfooj Khan Jul 25, 2018 1:10 AM (in response to Haan Haan)Hi,
As per your conditions,
In filters shelf you've kept
Name = Cheras and PJ
HQ = A
Searched Date = 1/5/2018
I don't know what is the logic behind this when you're selecting 1/8/2018 you're dividing Nearest day from today/10000 whereas in case of 1/5/2018 its Nearest day from today/1000. Could you please explain the exact logic for this?
However find attached the latest calculation
Mahfooj

Book6 (1).twbx 36.1 KB
