Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Sum Between two fields split by month Post Reply Post New Topic
Author Message
CryYs
Newbie
Newbie


Joined: 13 Nov 2008
Online Status: Offline
Posts: 1
Quote CryYs Replybullet Topic: Sum Between two fields split by month
     Posted: 13 Nov 2008 at 12:11pm
Hello,
 
This is a basic query in excel and also sql. I am building a report from an oracle table that is split as follows:
 
  Total Sales
Jan  $ 543.00 22
Feb  $0  0
March  $ 654.00 3
April  $ 456.00 4
 
I have created a bar grapgh in a subreport in crystal that will split by month. Basically I am looking to have the Total / Sales for each month in the graph.
 
Unfortunately, it seems that crystal is having trouble processing this request since I don't know the exact formula.
 
In SQL, it would be:
 
SELECT MONTH, (TOTAL / SALES) As AvgSales
FROM TABLE
GROUP BY 1
 
.. but I am having endless issues getting this across in crystal.
 
The main problem is the number of drill downs, since those months and values appear frequently. It seems that Crystal grabs the average of each month, rather than summing the sales buy the month/drill down ,summing the trans by the month/drill down and then dividing each from each other.
 
I would greatly appreciate any assistance since I have been struggling with this for weeks.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 14 Nov 2008 at 6:22am
Sounds like this would work.  I hope that it is not something that you have already tried.
 
Create a group by the month, this will get all of the one month together.  You can write a formula in Crystal that sum by the group SUM(field to sum, group to sum in) and Count(field to count, group to count in).
 
Or you could write a stored proc that that gets your values and performs you calculations there and just returns them to the report.
 
Hope this helps
IP IP Logged
inetuser09
Newbie
Newbie


Joined: 19 Jul 2008
Online Status: Offline
Posts: 2
Quote inetuser09 Replybullet Posted: 18 Nov 2008 at 12:48am
Hi, I am still having problems

Group1 Group2 Month Spent Transactions
Z A1 May $56,346 6
Z A1 June $46 6
Z A2 May $456 5
Z A2 June $45 4
D B1 May $756 3
D B1 June $45 2
D B3 May $45 1

Image the top is the data set

My report has many drill downs

I group by Group1 then Group2.
I have a graph split by month on the x axis.

I want to get the average transaction $ amount per month after the groupings.

That means the total of Spend in the grouped month divided by the total transactions of the grouped month.

At the moment, I am getting the sum of each average for each month, which is wrong.

Can you please advise how I can perform the calculation correctly?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Nov 2008 at 6:14am
Just to make sure, z/a1/May should be 56346/6. If so, create a formula, say group2Avg and in it put Spent/Transactions or if these are already sums, not the raw details then SUM(Spent, Group2) / COUNT(Spent, Group2). 
If you put the average function in the details and then report on the average, the number will be meaningless.
This is how I would do if for a report...my company doesn't use charts, so I am not familar with them.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.