| Author |
Message |
katie1200
Newbie
Joined: 29 May 2009
Online Status: Offline
Posts: 23
|
  
Topic: calculate sales total minus returns
Posted: 30 Jun 2009 at 1:04am |
hi all,
i need a formula or some way to calculate the sales total minus any returns.
i am using summary to sum the sales.quantity field but i need to take into consideration the sales.returns field to get the net sales value.
i dont know if the running totals or the formula workshop is the way to go here?
i am not very familiar with crystal syntax or formulas so any help would be great.
tanx a mil,
katie.
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 30 Jun 2009 at 6:29am |
in a formula, you could SUM({sales.quantity}, reportgroup) - SUM({sales.returns}, reportgroup) and place the formula in the group footer.
If you need the syntax, you can have Crystal create the summation and then either copy it or reference it in the formula.
HTH
|
| IP Logged |
|
katie1200
Newbie
Joined: 29 May 2009
Online Status: Offline
Posts: 23
|
Posted: 30 Jun 2009 at 7:06am |
i was thinking somethig like that but the problem is i am adding the "quantity" field and a sale or return is specified in the "type" field by I for invoice or C for credit so i need to add all the I quantities and all the C quantities and take them from each other but i have no clue how to do this i am playing around with the formula editor but i dont really know the syntax or how to create it automatically in crystal as u mentioned in your above reply.
or is running totals the answer here?
thanks again lockwelle for your input apologies for the noobiness and lack of coding ability!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jun 2009 at 7:46am |
You can use 2 RTs here and subtract them in a formula.
If you just need an overall report # create one RT as:
RT Name="Sales"
Field to SUmmarize=Quantity
Type of Summary=SUM
Evaluate=USe a Formula as: {table.type}="I"
Reset=Never
Second Running Total do the same thing but name it "Returns" and make it evaluate on formula as {table.type}="C"
Create a "Total" Formula as:
{#Sales SUM}-{#Returns SUM}
|
| IP Logged |
|
katie1200
Newbie
Joined: 29 May 2009
Online Status: Offline
Posts: 23
|
Posted: 30 Jun 2009 at 8:11am |
thanks DBlank thats exactly what im looking for only one little issue!
i am gettin an error saying "this field name is not known" when i put {#sales SUM}-{#credit SUM} into the formula workshop i have followed ur instructions above exactly
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jun 2009 at 8:23am |
Sorry yours will be :
To validate you can grab both items from the Report fields in the Formula workshop...
When you go into the Formula Workshop look at the Report Fields and you should see your 2 RT fields (they will have the Sigma sign in front of them).
Double click on the Sales field and it will appear in the formula window, then type in the subtraction sign then double click on the Returns field.
Just to calrify the reason I gave you an incorrect fomula was I copied a formula from a test report and I had names for my RTs in that as "sales SUM" and "credit SUM". Sorry about that  |
| IP Logged |
|
katie1200
Newbie
Joined: 29 May 2009
Online Status: Offline
Posts: 23
|
Posted: 30 Jun 2009 at 8:41am |
DBlank saved the day again thanks a mil, this is the same report im trying to finish for the last two weeks everytime i get it done something else needs a slight change!
hopefully this will be it then onto a crosstab report!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Jun 2009 at 8:51am |
Glad you got it to work 
Lockwelle could have guided you to a solution using variables and I do highly recommend learning how to use them. They will do some things that RTs won't.
I find RT's easier to use and am trying to get better at variables myself.
Good luck on the crosstab ... they can be a lot of fun  |
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 30 Jun 2009 at 10:00am |
|
RT seem to do alot, and I mean to learn how to use them, but I find that I am using the same logic as I would for a formula with shared variables...So I just keep using them. Glad you could find a solution.
|
| IP Logged |
|
|
|
