Cross-Tab Forumla to Count and Sum 1

[bnsSteve]

Hi there,

I have a Cross-Tab set up that has a list of store location #'s by row and then a list of accounts across the
columns. It is totaling up the number of transactions by count and $sum of each each store # to each account

The count/sum of transactions fields are made up of 4 txn types (ex: DR, DR correction, Transfer, Transfer correction) and currently is counting for every type. I need to edit the count so that I can subtract the total number of txns by the other 3 txns types. (Similarly need this for the $value)

Is there a way to add in a formula to the cross tab that would give me a count of "DRs - DR Correction + Transfer - Transfer Correction?"

Thanks!



Running Crystal 11 - 14.0.2.364

 

[pmax9999]

Assuming I understand the problem correctly, create 2 formulas along the following lines:

[Code TranCount]
If {Table.TransType} in ['DR', 'Transfer']
Then 1
Else -1
[/Code]

and

[Code TranValue]
If {Table.TransType} in ['DR', 'Transfer']
Then {Table.Value}
Else -{TableValue}
[/Code]

Then insert a sum on each of these fields in the Cross Tab to replace the Count and Sum.

Hope this helps.

Cheers
Pete

 

[bnsSteve]

Hi pmax,

You're solution worked great! I did finally figure out a way to do it but my way involved creating a formula for each transaction description saying if txn descrip='DR' then 1 which I basically created 8 times, 4 for count and 4 for dollar values. Then created another formula that added and subtracted each formula.

You're way is MUCH better and will be using this method!

Thanks again for the solution, greatly appreciated.

Running Crystal 11 - 14.0.2.364