Distinct Sum 1

[dannyb29]

Hi All,

Is there a formula that I can distinct sum a value in a record.

For example:

Group 1: TransId 1010 Sales Value 2.00

TrnxId Product Product Price Sales Value
1010 biscuit 0.50 2.00
1010 coke 1.00 2.00
1010 orange 0.50 2.00


Many Thanks

 

[hilfy]

I assume you need a distinct sum of the Sales Value and that you're grouping by TrnxID, correct?

If that's the case, you'll need a formula to provide the distinct value. The formula will look something like this:

If PreviousIsNull({table.TrnxID}) or {table.TrnxID} <> Previous({table.TrnxID}) then {table.SalesValue} else 0

You would then sum this formula.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[IanWaterman]

Use a Running Total which sums SalesValue, but only evaluates on change of TrnxId and resets on change of TrnxId.

Ian

 

[dannyb29]

Fabulous thanks guys!!

Hilfy,
The first part works as individual records but will not allow me to sum either in a formula or in the report.

Dannyb

 

[hilfy]

Hmmm...That's odd. I've never had any problems doing that. What version of Crystal are you using?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[dannyb29]

Its Crystal Reports 2008

Dannyb

 

[lbass]

You can't insert sums on a formula that contains next() or previous() functions, since they are based on a sequence of records. You could use a variable to sum that or you could use the running total solutions, but both would require the result in the group footer.

It appears that you have one sales value per trnxID--if this is true, you could insert a maximum on sales value and then drag the result into the group header.

-LB

 

[dannyb29]

Thank you for this. However I was planning to use them Sim function in a crosstab within the same report. I cannot see how the running total would work in this instance.

Dannyb

 

[lbass]

??? Are you responding to me? "Sim"? Did you consider the maximum function?

-LB

 

[dannyb29]

Hi Ibass,
Sorry i didnt make myself clear
Your Max suggestion works perfectly in the detail section, but I need to sum all the max figures and combine the results into a crosstab.
The crosstab will show Days across the top and the total sales for that day in the detail of the crosstab.

This is the only accurate field I can use within the database. My prefered method would be multiplying retail price by quantity, but this way does not take into account variable promotional rates. Currently our promotion field is not working as it should so this the only other field I can use.

Dannyb

 

[lbass]

What version of CR are you using? You can use running totals in a crosstab. You would choose sum of sales value, evaluate on change of group trnxID. I would put the crosstab in the report footer though.

-LB

 

[dannyb29]

Thank you. I realised I was making a major school girl error. This has done the job nicely!

Many Thanks x
Dannyb x