Getting the average across two fields. Impossible in Crystal?

[internetuser09]

I woluld greatly appreciate any assistance with this problem since I have been attempting to solve it for weeks to no avail.

Basically, I am trying to get the average of two fields after grouping them and plotting them on a graph (effectively grouping by the X variable: Month)

The dataset looks like this:

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


My report has many drill downs

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

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?

[Ned Pepper]

So here is your issue, you have two conflicting statements:
"I group by Group1 then Group2"
and
"I am trying to get the average of two fields after grouping them and plotting them on a graph (effectively grouping by the X variable: Month)"

The month of May will appear four times in your groupings so how do you get the average?

The aggregate functions in crystal follow the same rules as in SQL. If you want a sum, average, or count by month, you must then group by month. You are grouping by Group1 and Group2, so that is what you can aggregate by.

If you want something else, try using running totals or a formula. I recommned the running totals as a formula here could get quite complicated depending on your data.

[internetuser09]

The month of May will appear four times in your groupings so how do you get the average?


I want to sum the total transaction sums in may divided by the total transactions in may.

I can see that the graph does group each month on its own, but does not speciifcally create a 'group' like the drill down groups

I can run this in SQL quite easily:

SELECT GROUP1, GROUP2, MONTH, (SUM(TRANAMOUNT) / SUM(TRANCOUNT)) As Avg
FROM TABLE
GROUP BY 1,2,3

Now I can see that the graph function groups the month on the X axis to create the bars. Why can't it perform the sum function by group?