Cross-Tab formula

[rhoneyfi]

I have a cross-tab report that lists salesman and date and summarzies by units sold

Ex:
Jan Feb March
Joe 4 5 10
Frank 2 1 4

What I would like is a formula that calculates the % percentage difference in units sold each month

ex:
Jan Feb March
Joe 4 5 10
0% 25% 100%
Frank 2 1 4
0% (50%) 300%

Any ideas?

 

[lbass]

I think you would need your report to be a manual crosstab if you want to incorporate the percentage change. So your columns would be comprised of a series of formulas like {@JanAmt}:

if Month({table.date}) = 1 then {units.sold}//repeated for each column

Then, assuming you have grouped on salesman, the formula for difference between months would look like:

if sum({@JanAmt},{Table.Salesman}) > 0 then
(sum({@FebAmt},{Table.Salesman})-sum({@JanAmt},{Table.Salesman}))%sum({@JanAmt},{Table.Salesman})

-LB

 

[rhoneyfi]

I am a little unfamiliar with manual cross tabs....can you point me in the right direction so I can learn how to make them. Thanks

 

[lbass]

In your example, you would just create a formula for each detail field

if Month({table.date}) = 1 then {units.sold}//this is for @JanAmt

if Month({table.date}) = 2 then {units.sold}//@FebAmt

if Month({table.date}) = 3 then {units.sold}//@MarAmt

Group on Employee and insert a sum on each detail field and drag the sum to the group header. Create one formula per column for the % difference between months and also place in the group header:

if sum({@JanAmt},{Table.Salesman}) >=0 then 0 //since January is the first month, there is no % difference.

if sum({@JanAmt},{Table.Salesman}) > 0 then
(sum({@FebAmt},{Table.Salesman})-sum({@JanAmt},{Table.Salesman}))%sum({@JanAmt},{Table.Salesman}) //formula for Feb

if sum({@FebAmt},{Table.Salesman}) > 0 then
(sum({@MarAmt},{Table.Salesman})-sum({@FebAmt},{Table.Salesman}))%sum({@FebAmt},{Table.Salesman}) //formula for Mar

Ken Hamady also has an FAQ on formulas that addresses manual crosstabs:

http://www.kenhamady.com/formula.html

-LB

 

[rhoneyfi]

lbass,
Thanks for the formulas, they work great!
The last thing I am trying to do is to now do a 'Top N' based on the new ' % difference' field ...

So lets say I run this report monthly, I want the top N employees who have negative '% difference' values to be displayed....I used the Top N tool, but Crystal will not allow me to do a Top N based on the % difference field....any ideas?

 

[lbass]

To limit the display only to those groups with negative % differences, go to edit selection formula->group and enter:

if sum({@JanAmt},{Table.Salesman}) > 0 then
(sum({@FebAmt},{Table.Salesman})-sum({@JanAmt},{Table.Salesman}))%sum({@JanAmt},{Table.Salesman}) < 0 or
if sum({@FebAmt},{Table.Salesman}) > 0 then
(sum({@MarAmt},{Table.Salesman})-sum({@FebAmt},{Table.Salesman}))%sum({@FebAmt},{Table.Salesman}) < 0

This will limit the display only to those Salesmen who have had negative % differences in at least one of the two months February or March.

You can't use TopN on formula fields using summaries, but you could perhaps further limit your records by creating a parameter to use in the group select formula to substitute for the &quot;0&quot;, for example:

Set up a parameter with the following number values:

0, -25, -50, -75, -100

And then change the formula above as follows:

if sum({@JanAmt},{Table.Salesman}) > 0 then
(sum({@FebAmt},{Table.Salesman})-sum({@JanAmt},{Table.Salesman}))%sum({@JanAmt},{Table.Salesman}) < {?percent}or
if sum({@FebAmt},{Table.Salesman}) > 0 then
(sum({@MarAmt},{Table.Salesman})-sum({@FebAmt},{Table.Salesman}))%sum({@FebAmt},{Table.Salesman}) < {?percent}

Then you can test which parameter displays the approximate number of salesmen you are looking for--but still not in order by percent. Depending on your data, you might be able to achieve an approximate order by creating a formula at the detail level:

{@FebAmt}-{@JanAmt}

You can insert a summary on this and do a TopN on it, but this assumes that the largest discrepancies result in the largest percentage decreases, and this might not be the case. I'm not sure if there is another way to achieve the order you're looking for.

-LB