Have a group based on an Average Forumla?

[Csukardi151]

Hello,

I created a report that gives a shipping analysis of either On-Time or Late.

Sometimes a single order will contain both "On-Time" parts and "Late" parts with multiple shipments. So I created a formula that says, if the part is late then mark the line as 0, if it is not late, mark it as 100.

Next, I made an average of that forumla I created. (which takes all the 0's and 100's of the parts per order and gives a legit average %)

All of the above is working great. Here is where my question comes in.

I created a new forumla. It says if the average = 100 then "On-Time" else "Late". I threw that into the report onto Group Header 1 line, which is grouped by order number. This works perfect.

But now I wanted to create a new group based off my new forumla to sort all the orders into "On-Time" or "Late". When I go to add a group, I do not see my forumla in the list, which makes me assume that it cannot sort by my forumla.

Is there a way to work around this? Please let me know if I need to be more clear. Thanks you!

~Steve

 

[lbass]

You could do a group sort if instead of creating a summary formula you just right click on your 0/100 formula and insert an average at the group level. You can then go to report->group sort and select the average to sort the groups by. If you need to create faux "group headers" for a higher order "Late"/"OnTime" group, let me know.

-LB

 

[Csukardi151]

Thanks for the reply LB.

The problem I see with a sort of the Average is that their will be multiple fields (ex. 100%, 88.5%, 50%... etc)

I would like to aim for a group header, if possible, to just group by 2 categories. On-Time (100%) and Late (99% to 0%).

~Steve

 

[lbass]

Okay, please post the content of the formula that results in 0 and 100. Also identify the fields you are grouping on in the report using the convention {table.field}.

-LB

 

[Csukardi151]

Ok my first grouping is {table.OrderNumber}. I also have the Average of @Late (See Below) in this Header. Now the order number can have multiple ship dates so I identify shipdate per part.

In Details:
{table.OrderNumber} {table.Part} {table.ShipDate} {table.DueDate} {@Late}

This is my 0/100 Formula (I called this {@Late}):
if {table.ShipDate} <= {table.DueDate} then 100 else 0

This is an example of my results:

GH1: 8115 50%
D: 8115 SR5 8/10/10 8/11/10 100
D: 8115 MR81 8/13/10 8/11/10 0


Now I created a new formula that basically says:
if Average ({@Late}, {Table.OrderNumber}) = 100 then "OnTime" else "Late"

This also works, if I put that into a GH1, it will put "On-Time" next to the 100's and "late" next to everything else.

This is where I tried to make it the highest tier group so I can drill down on OnTime & Late.

 

[lbass]

Please provide your ACTUAL table and field names, not "table". I'm going to suggest a SQL expression, but need to see what tables are involved. Also please identify the type of database.

-LB

 

[Csukardi151]

Database is Pervasive 9.5

All tables used are the same: V_Order_Hist_Line

V_Order_Hist_Line.Order_No
V_Order_Hist_Line.Part
V_Order_Hist_Line.Date_Shipped
V_Order_Hist_Line.Order_Due

@Late:
if {V_ORDER_HIST_LINE.DATE_SHIPPED} <= {V_ORDER_HIST_LINE.DATE_ORDER_DUE} then 100 else 0

 

[lbass]

Try setting up a SQL expression {%avg} like this. I'm not sure about the punctuation/syntax for pervasive though, so double click on fields from the field list to see how they display:

(
select avg(iif(`Date_Shipped`<=`Date_Order_Due`,100,0))
from V_Order_Hist_Line A
where A.`OrderNumber` = `V_Order_Hist_Line`.`OrderNumber`
)

Note that within the avg(), you should not reference the table name if you are using a CR version between 9 and XI. Be sure to allow the alias table "A" to remain as is.

Once you have the SQL expression working, create a formula like this:

if {%avg} = 100 then
"On Time" else
"Late"

Insert a group on this and make it your outer group.

My original approach would have worked to. There is a way you can do the group sorting and then insert another group header section_a that is conditionally suppressed except at certain points, allowing it to appear as if it were an outer group. However, the SQL expression approach is superior as you can then work with that group for other summaries, etc.

-LB

 

[Csukardi151]

LB,

Thanks for your help and feedback.