Trying to compare two $ values in two separate columns 1

[clanm]

I've got two columns I'm trying to compare on my report, and only return records where there's a difference. This will allow the user to see inconsistencies.

The first column is one - to - one with the main table, and the other column is a one - to - many with the main table, and I have to divide this second column by 1000 in order to compare the two.

I can visually see the dollar amounts are the same (or different), but can't get the report to see that. Both totals are per PK in the main table. The first column is simply the value of the column, where the second is the summation / 1000.

Any ideas are welcome!

Thanks!

 

[lbass]

Sample data would help. It sounds like you are describing something like:

5 200
5 400
5 800
5 1600
5 3000/1000 = 3 //comparing 5 vs. 3

If this is the case, then insert a group on {main.pk} and then go to report->selection formula->GROUP and enter:

maximum ({table.col1},{main.pk}) <> sum({table.col2},{main.pk})/1000

-LB

 

[clanm]

lbass,

Thanks!

Tried that, and same thing.

Still don't get the comparison correct. I've tried WhilePrintingRecords; too, and that's not helping. Both are decimals data types.

 

[lbass]

Please show a sample of your detail level records and the group level results you are getting. Then show the results you expect to get. Be sure to describe your group structure, and if you have any section suppression or group selection formulas, please share the contents.

-LB

 

[clanm]

lbass,

You hit the nail on the head with the data of:

5 200
5 400
5 800
5 1600
5 3000/1000 = 3 //comparing 5 vs. 3

I have one grouping which is the PK for the main table.

When looking at the report I see the following as a result using a forumla to view the comparision:
No 2,075,091.00 0.00
Yes 0.00 0.00
No 2,391.33 2,391.33

The above first and second are correct, but the third isn't.

I did notice one thing...that if I take the \1000 off the second column, and do * 1000 to the first column, the two places after the decimal point are not matching. I just don't understand if I keep the \1000 on the second column and the data values are the same. I made sure the format for each column were the same.

 

[lbass]

I don't know what it is you are showing here--it looks like you are showing group results instead of the detail and group levels. Also, I don't know what the columns represent. Please also share the formula you are using for the comparison. I don't know what the problem is--it now sounds like this is an issue of rounding or something? If it is a rounding issue, then you could replace each column with a rounded version, e.g.,

//{@col1}:
round({table.col1},2)

//{@col2}:
round({table.col2},2)

Then use the group selection formula, substituting these formulas for the fields.

-LB

 

[clanm]

lbass,

Thanks for the tip on the round() function. I think that's going to do the trick.

My last reply was showing the data in the grouping section for the main table's PK.

Looks like because one field is stored as $K dollars, and another is stored as actual.....there are small discrepencies......meaning the two decimal places to the right will more than likely be different.

thanks again for the help!