Hello,
I have a dataset returning sales figures with dates and consultant codes.
The table I'm presenting the data in groups the information firstly by Month and Year, then by Consultant Code.
I need to provide a running total for each consultant code that accumulates over the months.
Eg Required Output would be:
Jan 2008
Consultant Code Sales Consultant Running Total
A1 50 50
A2 100 100
Feb 2008
Consultant Code Sales Consultant Running Total
A1 49 99
A2 75 175
I've tried the runningvalue function, but it doesn't seem to give me what I want.
When I use it with the scope of the dataset, it accumulates but does not factor in the Consultant Code Grouping and so acts as a running total for all Consultant Codes.
When I use it with the scope of the Month Year Group, it accumulates but obviously resets on each Month Year Group and does not factor in the Consultant Code grouping.
When I use it with the scope of the Consultant Code Grouping, it totals by the Consultant, but only within the Parent Group of the Month Year Group.
When I use it with the scope of Nothing this gives the same behaviour as the scope of the dataset - providing an overall running total.
I recognise that I will probably have to use some sort of expression with the runningvalue function, but so far everything I've tried has not worked. Eg. have 'aggregate in aggregate error messages, etc.
I realise I may be missing something really obvious here and if so would appreciate being told what it is!
I'm sure this is much easier in Crystal
Thanks in advance for any suggestions you can provide.
Cheers,
Sarah
I have a dataset returning sales figures with dates and consultant codes.
The table I'm presenting the data in groups the information firstly by Month and Year, then by Consultant Code.
I need to provide a running total for each consultant code that accumulates over the months.
Eg Required Output would be:
Jan 2008
Consultant Code Sales Consultant Running Total
A1 50 50
A2 100 100
Feb 2008
Consultant Code Sales Consultant Running Total
A1 49 99
A2 75 175
I've tried the runningvalue function, but it doesn't seem to give me what I want.
When I use it with the scope of the dataset, it accumulates but does not factor in the Consultant Code Grouping and so acts as a running total for all Consultant Codes.
When I use it with the scope of the Month Year Group, it accumulates but obviously resets on each Month Year Group and does not factor in the Consultant Code grouping.
When I use it with the scope of the Consultant Code Grouping, it totals by the Consultant, but only within the Parent Group of the Month Year Group.
When I use it with the scope of Nothing this gives the same behaviour as the scope of the dataset - providing an overall running total.
I recognise that I will probably have to use some sort of expression with the runningvalue function, but so far everything I've tried has not worked. Eg. have 'aggregate in aggregate error messages, etc.
I realise I may be missing something really obvious here and if so would appreciate being told what it is!
I'm sure this is much easier in Crystal
Thanks in advance for any suggestions you can provide.
Cheers,
Sarah