Percentage of total in crosstab

[pam1980]

Hello,

This seemed easy enough but I am struggling and would really appreciate some help.

I am trying to create a crosstab for how many days it took to complete an order. These are the formulas I created (the first part is the name of the formula):

1) days took to close - Date difference in days (d) between date ordered and date closed
2) < 20 - if {@days took to close) < 20 then 1
3)< 40 - if {@ days tool to close) >= 20 and {@ days tool to close) <= 40 then 1
4)> 90 - if {@ days tool to close) > 90 then 1
5) {@<20} + {@<40} + {@>90}

In the cross tab I pulled in formulas 2, 3, 4, 5 mentioned above under the summarized fields (which displayed it as sum of ....)
Nothing at all in the rows and columns field in the cross-tab query expert.

In the report design, I manually added labels for the 3 different rows as: < 20, <40 and >90

So far so good. But tried a lot and I am not able to create percentages that work (tried so many different things).

There is no grouping in the report as there are no details. We just need a cross tab with totals and percentages. And a chart which I will worry about later.

Thank you,

Pam

 

[Madawc]

If you group by your date range, you can compare the group total to a summary total for the entire report.

I'm not sure if this can be used in a crosstab, probably not. You could show the data as a subreport instead, placing the subreport in the report footer.

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I think you should create a formula like this to use as a row field:

select {@datediff}
0 to 19 : "00 - 19"
20 to 39 : "20 - 39"
40 to 59 : "40 - 59"
60 to 89 : "60 - 89"
90 to 10000 "90+"

Not sure why you left out ranges for 40 - 89, but you could omit them. Then the total column would show the combined value.

You would add this as your row (or column field) and then add a summary field like distinctounct of Order ID, which would show the number of orders that fell within those datediff ranges. You could also add the summary again and choose "show summary as percentage".

You should provide your version of CR as crosstab functionality and the use of percentages varies by version.

-LB

 

[pam1980]

Thank you all for the post responses.

I have version 11.


May I rephrase my query please. Since a couple of items have changed and the cross-tab summary did not work.

Say I have a formula field to calculate the days between order taken and last time it was sent for closure. There are several dates for closure and only the last one in the table is relevant so the formula is:

1)maximum ({table.field}, table.id}) - {date created}

then I want in the footer:
2) total closed in given time range - can be done on report itself
3) count of days < 20 and the percentage of total
4) count of days > 20 and < 50 and the percentage of total

When I try to use the formula with maximum I am unable to do a count - message it can't be summarizzed. What can I do to calculate #3 and # 4 above.

Thanks much,

Pam