Conditional group formula

[veles]

Hi,

I am usig crystal XI. I need to create a formula that will count stores the sold a specific item(bike) in a calendar year grouped by a formula.

I created group 1- calendar year (used salesdate and did yearly)
For group 2 I created a formula to count the sold item (1-100, 100-500 and over 500)
Group 3 is the stores
aded a sum at group 3 level just to verifiy that the store is in the right group 2.

For some reason some stores apear twice under group2(once as 100-500 and again over 500.

I need to midify the group 2 formula where I will get the stores under the right group2 according to the total sale for the year.

If I take out group 2 then the store shows only once in the year with the correct ammount of bikes sold.

What am I doing wrong.

Any help appreciated.

Thanks

 

[pmax9999]

It would seem the issue is in the formula used in Group2 to count the sales. Please provide the contents of that formula, and any other formulas referred to in that formula if there are any.

 

[veles]

Thanks for the reply.

The formula is

if {sales.value}in 1 to 100 then "1-100" else
if {sales.value}in 100 to 500 then "100-500" else
if {sales.value}> 500 then "Greater than 500"

No thinking about it the sales value is a sum for the year. Could that have anthing to do with the formula just beeing the field

 

[pmax9999]

One obvious issue in your formula is that sales of 100 would be included in both "1-100" and "100-500".
Can you explain please the structure of the sales table. Is there 1 row per year, or 1 row per sale? Assuming the latter, your result would be explined by 1 sale of 100-500 units, and another of more than 500 units. This would require a different approach as it would be necessary to sum the sales first.

 

[veles]

Ok I will fix the formula to say 1-100, 101-500, =>501

The sales is units for each day(1 row per sale, daily).

So in group 3 i have the store and then a sum in group footer 3.

Group 1 is salesdate and yearly is selected in the change group

 

[pmax9999]

OK, that makes sense. The issue here is that you need to to add a group (Grp 2) based on the result of a calculation not completed until Group 3, which is not going to work.

There are a couple of ways this can be achieved, and requires that the total sales be determined at the outset. The best approach is likely to be a Crystal Command that returns a single row containing Year, Store and Total Sales and whatever other data you require for the report.

If you post the SQL generated by the report as it currently stands, we will be able to assist you create that command if you are not familiar with them.