Show montly variance percent in a cross-tab

[tawke]

Hi,

I have Cross-tab which i have set up to show Montly values, currently it just shows the values (10, 15, 5, 30) What i would like to add is to show the variance\difference from month to month between these values like in the example below

Jan Feb Mar Apr
User 1 10 100% 15 50% 5 -66,66% 30 500%


Any ideas on how to accomplish this?

 

[lbass]

To do this, you would have to create conditional formulas for each month, so that the months you want to compare are available for the percentage--which means it is probably just as simple to create a manual crosstab, where you would have two formulas per month. First you would group on user and then create formulas like this:

//{@Jan}:
if month({table.date}) = 1 then
{table.amt}

//{@Feb}:
if month({table.date}) = 2 then
{table.amt}

Insert sums on these formulas at the group level and then suppress the detail section (drag the groupname into the group footer). Then create one change formula per month like this:

//{@Feb%chg}:
(sum({@Feb},{table.user})-sum({@Jan},{table.user}))%
sum({@Jan},{table.user})

If your date range contained more than one year, you would need to modify the formulas to take the year into account.

-LB

 

[tawke]

Thanks alot for the reply, i agree that a manual cross tab would be best to use here.

I cant seem to figure out how to setup the formula for it to Count the amount of calls, in addition to filtering it on dates

I have grouped my report on {Service_Calls.Service}, i have DateTime entry {Service_Calls.Created}

What would the {table.amt} field be in my case?

 

[lbass]

If you don't have duplicating data, just replace {table.amt} with 1. Be sure so insert a sum on the formula, NOT a count.

-LB

 

[tawke]

Thanks alot, that did the trick

Next challenge
I am mostly interested in showing the Services with either a high or low percent variance, is there any way i can do group sort based on the percentage value i get from that formula? I can only seem to sort on the fields added by the "Insert Summary" function, and not the formulas i just drag into the group

 

[lbass]

That's correct--you cannot do a group sort on the percentage. You would have to convert the summaries to SQL expressions, then create the percentage formula, and then insert a maximum on the result in order to make it available for a group sort.

-LB