Skip to Content
0

Crystal Formula Issue

Feb 15, 2017 at 05:54 PM

205

avatar image

  • 1)Body values of column A&B are formulae
  • 2)I need a formula for calculating ‘Sum A’ & ‘Sum B’ which will add only body values of group3.
  • 3)Once these Sum A & Sum B are available in formula, I can use these formulae to calculate
  • Sum A/ Sum B to be placed in group3 of column C.

I have already tried following which are not working.

  • I.Insert totals of column A & B, but they are not available in formula to be used.
  • II.=sum (@bodyformula), this summing up everything in column A
  • III.Weighted average in column C group3 header, giving some random values

Kindly help. Thanks in advance. Kind Regards.

Naoman D.

sum-formula.png (53.8 kB)
10 |10000 characters needed characters left characters exceeded

..........

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Abhilash Kumar
Feb 16, 2017 at 11:43 AM
0

Why do you have the same field inserted as a group thrice?

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Yes we have different group of account ranges, top group is parent group with account ranges (say 1 to 10).

Group 2 will have sub range category say (1 to 2) & (3 to 4)......(9 to 10)

and group 3 is created for summarize data in order to avoid line items.

I can use formula (Sum A /Sum B) but only for any one of the group.

For rest their corresponding totals are not available to be used in formula. Thats why I need formula to sum up body values so that I can use them in column c calculations. Kindly suggest.

0
Ian Waterman Feb 16, 2017 at 09:03 AM
0

What is the formula used in @bodyformula

If it contains an aggregate function or a variable you can not use the Crystal sum function.

You then have to use either a variable or Running Total, however these can only be used in a group footer. It looks like you want to display in header, which will not be possible.

Ian

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Ian,

Body formula is like

if date = {@parameter} then measure.

Thanks. I'll check ur proposed solution,

0

In footer also we can put. Could you please suggest code for the same. Thanks

0

You need a default condition it should then be available to sum

Try

if date = {@parameter} then measure else 0

Ian

0
Abhilash Kumar
Feb 16, 2017 at 09:07 AM
0

Hi Naoman,

1. Sum({@Column A formula}, {Group 3 field})

This will return sum of column A only for Group 3. Place this formula on the Group Footer 3.

2. Use a similar formula for Column B.

3. To divide these two, use:

If Sum({@Column B formula}, {Group 3 field}) = 0 then 0

else

Sum({@Column A formula}, {Group 3 field}) / Sum({@Column B formula}, {Group 3 field})

-Abhilash

Show 2 Share
10 |10000 characters needed characters left characters exceeded

All this assuming both formulas for Column A and B are not using print time functions.

-Abhilash

0

Hi Abhilash,

Thanks for your reply. No there is no print time function in column A & B.

I tried your solution, it gave incorrect results.

What I mean is when I put Group 3 field (Account Number), it is giving sum of all the values.

However all the groups (Group 1 & Group 2) using same field, that might be the reason.

And that might be the reason why totals are not available in formula.

Any further suggestions.

Thanks

0