1 2 Previous Next 20 Replies Latest reply on Jul 24, 2018 9:49 PM by Haan Haan

# Get differences of a calculation

Referring to the attached sheet 6 and the screenshot.

The number is incorrect...

It should be 0.1548 for nearest calculation 3 and 0.1548 for the diff calculation 3.

(0.1057-0.1548)/0.1548 = 0.3172

May I know how to fix it?

It is a continue from To have all measure names appear

Mahfooj Khan Due to urgency, I have opened a new thread to allow more discussion. I hope you won't mind....Sorry...

• ###### 1. Re: Get differences of a calculation

Hi, Haan

Please find my solution attached. below is the screenshot of result for your reference.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Get differences of a calculation

Dear ZZ

May I know is the logic of the "nearest cal 3" in terms of the date is it function the same as the "Nearest day from searched date"

stated below?

thanks

• ###### 3. Re: Get differences of a calculation

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

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

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

1 of 1 people found this helpful
• ###### 6. Re: Get differences of a calculation

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

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

Dear ZZ

Thanks.

• ###### 9. Re: Get differences of a calculation

Hi, Haan

Can I ask why you need link the date for both data sources?

ZZ

• ###### 10. Re: Get differences of a calculation

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

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

Dear ZZ

Nope..the reason of /2 is part of the calculation.

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)

• ###### 13. Re: Get differences of a calculation

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

• ###### 14. Re: Get differences of a calculation

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 3-Nearest 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

1 2 Previous Next