Hello all, I have a bit of a problem (as you can see from the subject).
I have a table that is a mix of raw data from the DB, and calculated fields in crystal. What I need to be able to do is have them all display nicely in a crosstab style chart.
here is the data layout:
|CurrentMonth-13|...|Current|6Month |fiscal |
| | |Month |Rolling|Year to|
| | | |Average|Date |
==========================================================
supportCalls |int from DB | | |Calc |Calc |
otherCalls |int from DB |...| |Calc |Calc |
TotalCalls |calculated |...| |Calc |Calc |
emails |int from DB |...| |Calc |Calc |
TotalActivity|int from DB |...| |Calc |Calc |
6 Month Avg |calculated |...| | | |
Baseline |int from DB |...| |Calc |Calc |
Variance |calculated |...| |Calc |Calc |
Variance % |calc |...| |Calc |Calc |
==========================================================
(hopefully that looks like a crosstab
There will be 15 columns, starting at 12 months from the current month to the current month, then 2 calculated columns of the 6month (rolling) average, and the Fiscal Year to date (Starting in april). I am having a problem getting the data into an array, or some form of formula to calculate the rolling (or moving if you prefer) averages. The rolling average is a calculation of this month's total activity added to the previous 5 months then divided by 6. Does anyone have any ideas as to how to accomplish this? I have tried creating a manual cross tab as well as using the crosstab expert, neither of which has been successful. Thanks for your time.
I have a table that is a mix of raw data from the DB, and calculated fields in crystal. What I need to be able to do is have them all display nicely in a crosstab style chart.
here is the data layout:
|CurrentMonth-13|...|Current|6Month |fiscal |
| | |Month |Rolling|Year to|
| | | |Average|Date |
==========================================================
supportCalls |int from DB | | |Calc |Calc |
otherCalls |int from DB |...| |Calc |Calc |
TotalCalls |calculated |...| |Calc |Calc |
emails |int from DB |...| |Calc |Calc |
TotalActivity|int from DB |...| |Calc |Calc |
6 Month Avg |calculated |...| | | |
Baseline |int from DB |...| |Calc |Calc |
Variance |calculated |...| |Calc |Calc |
Variance % |calc |...| |Calc |Calc |
==========================================================
(hopefully that looks like a crosstab
There will be 15 columns, starting at 12 months from the current month to the current month, then 2 calculated columns of the 6month (rolling) average, and the Fiscal Year to date (Starting in april). I am having a problem getting the data into an array, or some form of formula to calculate the rolling (or moving if you prefer) averages. The rolling average is a calculation of this month's total activity added to the previous 5 months then divided by 6. Does anyone have any ideas as to how to accomplish this? I have tried creating a manual cross tab as well as using the crosstab expert, neither of which has been successful. Thanks for your time.