Hi I'm putting together a report and I'm struggling with a particular aspect of it.
Below is some sample data from my details section.
| Account |
Value 2010 |
|
Value 2011 |
|
Check |
|
Difference |
| 1 |
12.00 |
|
13.00 |
|
0.00 |
|
1.00 |
| 1 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
| 1 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
| 1 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
| 1 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
| 2 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
| 2 |
0.00 |
|
13.00 |
|
0.00 |
|
13.00 |
I need to calculate the difference between the 2010 and 2011 values which is easy enough and as you can see it is currently doing that.
The tricky part (for me) is when there isn't a value for 2010 within any of the data returned for a particular account as I need the difference formula to return 0.
So for the data above, where there are two rows for account 2 the value 2010 is 0 for all rows with that account and therefore the difference column needs to show 0 because of this.
If you look at the rows where the account equals 1 there is one row where the value 2010 equals 12 although the rest of the rows with this account equal 0. I need a method of recognising that there has been one row of data for this account that has a figure greater than 0 in and therefore the difference formula is value 2011 - value 2010.
Hope this explains what I'm trying to do.
IP Logged
