Group On One, Sort on Other

[rbh123456789]

CR 11.5

I have a bit of an issue right now. I'm pretty sure i know the answer, but need a fresh perspective.

Currently, I am Grouping my date on some CaseID's.
The problem is that i need to order the records by the Date field of the record. There are multiple dates per CaseID.

I am using a forumla (thanks to lbass) which displays the appropriate date based on some business logic. Forumula below.

if isnull({table.date1}) then
(
if isnull({table.date2}) then
{table.date3} else
if not isnull({table.date2}) then
{table.date2}
) else
{table.date1}

The problem is that since i am grouping on the CaseID, i cannot order by the date field to list them oldest to newest.

Any ideas!!????

 

[lbass]

Right click on the formula->Insert summary->maximum at the case ID group level. Then go to report->group sort->all->select minimum of {@date} and choose the order--ascending/descending.

This assumes you want to order the IDs based on the first date per case ID.

-LB

 

[rbh123456789]

Thanks for the fast reply lbass, i really appreciate your help.
What you suggested is a great idea, and the report is almost there.

Though is there a way for the @dates formula to retain the logic in determining what date to display? Minimum will pick the oldest date, and Maximum chooses the newest date (instead of picking the right date based on the formula logic)

 

[lbass]

The formula will only return one date, so it IS following the logic. Please identify the fields as you did in your last post (which I now recognize) and then explain what you were expecting to see. It sounds like you were hoping to prioritize a certain TYPE of date in the sort. Maybe show some sample data with all three dates at the detail level, and then explain how you expect them to be sorted.

-LB

 

[rbh123456789]

I understand.

Okay, here was your formula from the last post.

//@date
if isnull({table.orderdate}) then
(
if isnull({table.deliverydate}) then
{table.installationdate} else
if not isnull({table.deliverydate}) then
{table.deliverydate}
) else
{table.orderdate}

You can see from my attachment that when mulitple dates are present the @date formula should pick (1) Delivery_date, if that date doesnt exist show (2) Installation_date, and if that date doesnt exist (3) Order_date

 

[lbass]

I thought you changed the formula when you specified the order above. It should be:

//@date
if isnull({table.deliverydate}) then
(
if isnull({table.installationdate}) then
{table.orderdate} else
if not isnull({table.installationdate}) then
{table.installationdate}
) else
{table.deliverydate}

-LB

 

[rbh123456789]

sorry. i copied the wrong formula.

This is the @date that is being used:

if isnull({table.installationdate}) then
(if isnull({table.deliverydate}) then
{table.orderdate} else if not isnull
({table.deliverydate}) then
{table.deliverydate}) else{table.installationdate}

 

[lbass]

Wrong formula. Please see my last post.

-LB

 

[rbh123456789]

thanks again for all your help.

unfortunatley, the results (even with your formula above) have similiar output.

please see the attachment, which shows @date using the installationdate before using the deliverydate.

This is why i believe the issues lies within the MIN summary, which is why @date is choosing installationdate since its the oldest date.

 

[lbass]

Please clarify this. I'm assuming that each row of data may include an installation date, delivery date, and order date. You seem to be saying that there are multiple rows per case ID. Why would that be? The formula I gave you would only return one date per row, and it would reflect your priority WITHIN the row. So you seem to be suggesting that you want to apply the same priority across rows--but why? What is the logic? If there are multiple {@date}s, what is unique to each row?

-LB