Find the difference between values grouped by month

[raja2008]

Hello Everyone!

Using Crystal XI R2 and SQL Server

I looked around and saw many threads with similar issue and tried the solutions and nothing worked for me.
I tried working with Crosstab and got nowhere.

Here is my problem

I have a DateRange for the user to pick the dates

Then this is how i would like the results to be displayed

By Type Month#1 %Diff Month#2 Month#3
//based on the dates the user picks


Network Issue 100 (100-200)/200 200

The % Diff is the diff 0f current month total and next month total divided by next month total.
(Month#1-Month#2)/Month#2


It would be great if i can get this in a crosstab.

Thank you in advance.

 

[lbass]

Are you saying you would want this percentage only for the first month compared to the next month or do you want this for each month? Is there any maximum number of months? Would they always fall within the same calendar year?

-LB

 

[raja2008]

No this will continue for each month and the user can pick from any day to any day.

 

[raja2008]

Hello lbass!

Here is a idea.
I'm thinking if i can get the next month totals from a subreport that sits at the group footer of the group that's on the date field by month and then perform the calculations by bringing the total of next month through a shared variable.

I have a Daterange on the main report.

Any ideas on how to get started.

I'm stuck on how to pull tyhe next months data in the subreport.





Thank You

 

[lbass]

Sorry, I lost track of this thread. If you really need the results in the horizontal format, I think you are better off creating a manual crosstab where you insert a group on type, and then set up conditional formulas:

//{@Month1}:
if {table.date} in dateserial(year({?Start}),month({?Start}),1) to
dateserial(year({?Start}),month({?Start})+1,1)-1 then
{table.amt}

//{@Month2}:
if {table.date} in dateserial(year({?Start}),month({?Start})+1,1) to
dateserial(year({?Start}),month({?Start})+2,1)-1 then
{table.amt}

//etc.

Place these in the detail section and insert sums on them at the group level and drag the groupname into the group footer. Then suppress the group header and detail section. Then create differenc formulas:

//{@DiffMo1Mo2}:
(sum({@Month1},{table.type})-sum({@Month2},{table.type}))%
sum({@Month2},{table.type})

//etc.

-LB

 

[raja2008]

Hello LBass!

Thank you very much for your response sorry for the delay.

That is exactly what i ended up doing.