Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cross-Tab Forumla to Count and Sum 1

Status
Not open for further replies.

bnsSteve

Technical User
May 3, 2010
94
CA
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top