count needed for a formula with 'maximum' in it

[pam1980]

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:
2) total closed in given time range - can be down 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. What can I do to calculate #3 and # 4 above.

Thanks much

 

[fisheromacse]

I am not sure what the requirements you would need for #2 are.

For #'s 3 and 4, i think you can create a formula like this:

//{@less20}
IF maximum (({table.field}, table.id}) - {date created}) < 20 then 1 else 0

then use the above formula in a summary

and do the same for #4.
//{@20to50}
IF (maximum (({table.field}, table.id}) - {date created}) > 20)
and (maximum (({table.field}, table.id}) - {date created}) < 50) then 1 else 0

 

[lbass]

Please do not start new threads on the same topic: see thread149-1648993.

You can do the above with running totals, by doing a distinctcount of {table.ID}, evaluate using a formula:

maximum ({table.date}, {table.id}) - {date created} < 20

Reset never.

In the second running total, use:

maximum ({table.date}, {table.id}) - {date created} in 20 to 49

Then you can use formulas that reference the running totals for the percentages:

{#<20} % distinctcount({table.id})

{#20-49} % distinctcount({table.id})

To chart, you would add the running totals individually. You won't be able to chart the % this way though, so you might be better off replacing the maximum with a SQL expression {%maxdt}:

(
select max(`date`)
from table A
where A.`ID` = table.`ID`
)

Then create a formula:

select datediff("d",{table.createddate},{%maxdt})
case 0 to 19 : "<20"
case 20 to 49 : "20 to 49"

Then use this as your "on change of" field in the chart and use distinctcount of ID as the summary field (choose as Show Percentage) if you wish to chart the percentages.

-LB

 

[pam1980]

Thanks lbass - that worked and I learned something new. I am not a technical user but both were my developers were out and I needed help - since I did not get a response to the earlier loop - I thought I would rephrase the subject since the content of the request had changed.

Thanks fisheromacse for your response as well.