cancel
Showing results for 
Search instead for 
Did you mean: 

Amount from lastest date minus the value from the oldest date

Former Member
0 Kudos

Hello,

i have some values in the body of my report which show by date values.

I would like to take the latest dates value and minus it from the oldest dates value.

e.g

date         Amount

1/3/12  = 45

1/4/12  = 30

1/6/12  =65

1/21/12 = 89

basilcay 89 - 45 is what i am after.

I have tried so far

maximum(Amount) - minimum(Amount) the issue is that it is working off 89 - 30...

any ideas?

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi David,

Here's what you can do:

1) Create a formula with the code below and place it on the details section. You may choose to suppress this formula too:

WhilePrintingRecords;

numbervar min;

numbervar max;

if Minimum({Sheet1_.Date}) = {Sheet1_.Date} then

min := {Sheet1_.amount};

if Maximum({Sheet1_.Date}) = {Sheet1_.Date} then

max := {Sheet1_.amount};

2) Create another formula to display the final amount and place this on the Report Footer:

WhilePrintingRecords;

numbervar min;

numbervar max;

max - min;

Hope this helps!

-Abhilash

Former Member
0 Kudos

Hello Abhilash,

Thanks for the reply, it does not seem to be working that well. The issue is, in the group footer i am trying to show the result, but the actual data is in the body..

Former Member
0 Kudos

maximum(Amount) - minimum(Amount) calculates for all values in the report.

Do two summaries of the amount in the group footer,

minimum(amount)                  maximum(amount)

now create a formula using the group footer summaries to calculate the difference

abhilash_kumar
Active Contributor
0 Kudos

Hi David,

I'm sorry I did not know that there is group in the report.

Just modify the first formula like this:

WhilePrintingRecords; 

numbervar min; 

numbervar max; 

if Minimum({Sheet1_.Date},{group_field}) = {Sheet1_.Date} then 

min := {Sheet1_.amount}; 

if Maximum({Sheet1_.Date},{group_field}) = {Sheet1_.Date} then 

max := {Sheet1_.amount};

Then. create another formula to reset the variables. Place this in the group header:

WhilePrintingRecords; 

numbervar min := 0; 

numbervar max := 0; 

Hope this helps!

-Abhilash

Former Member
0 Kudos

Hello Abhilash,

I am unsure if the issue is due to Crystal Enterpris but even if i place the "

Minimum({Sheet1_.Date},{group_field}) = {Sheet1_.Date} then  min := {Sheet1_.amount};     " in a seperate formula it returns 0 ?

the maximum one works...

abhilash_kumar
Active Contributor
0 Kudos

Hi David,

This formula will show zero on all rows except where the value is smallest in the group.

That's why I asked you to suppress the formula on the details section.

If you wish to see the exact values in the formula, create another formula:

WhilePrintingRecords;   

numbervar min;   

numbervar max;

"Minimum :" & min & "Maximum :" & max;

Place this on the Group Footer and check if the Max and Min values are correct. Also, make sure you reset all the variables at the Group Header level as I mentioned in my previous post.

Let me know how this goes!

P.S: If none of this works, just attached the report to this post and I'll have a look at it. I believe you cannot attach a .rpt file to this post. So, change the extension of the .rpt file to .txt and then attach.

-Abhilash

Former Member
0 Kudos

We have decided to do this in the BW query with a measure. Seemed to be an easier solution.

Your solution above did work aswell.

kind regards

David

Answers (0)