Calculate percentage after aggregate grouping

We are measuring how many deliveries are shipped the day after the order is taken by the week. I have been able to build an aggregate query that gives me the count of shipments shipped the next day and the total count of shipments. The problem is the Rollup needs to happen before the percentage calculation is made. I am having difficulty in getting this timing to work. My only promising attempt was to create a top level subquery with only the company table in it linked to the lower level query on company ID, selecting the rolled up fields, and performing the division. This still came out with 0s indicating that the timing is still not right. So I gave up on that idea. For those interested in the challenge, I have included my query below. In the meantime we can breakout a piece of paper and a pencil to calculate the percentage. Ultimately, it is simply a timing issue.

have fun!

select
(DatePart(year, ShipHead.ShipDate)) as [Calculated_Year],
(DatePart(week, ShipHead.ShipDate)) as [Calculated_WeekNum],
(count(ShipDtl.PackLine)) as [Calculated_ShippedLines],
(Sum((case when ShipHead.ShipDate = OrderHed.OrderDate then 1 else 0 end))) as [Calculated_SameDay],
(sum(case when Datediff(day,OrderHed.OrderDate,ShipHead.ShipDate) = 1 then 1 else 0 end)) as [Calculated_NextDay],
(NextDay/ShippedLines) as [Calculated_NextDayPercent]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderHed as OrderHed on
OrderDtl.Company = OrderHed.Company
And
OrderDtl.OrderNum = OrderHed.OrderNum
and ( OrderHed.OrderType_c = ‘Parts’ )

inner join Erp.ShipDtl as ShipDtl on
OrderDtl.Company = ShipDtl.Company
And
OrderDtl.OrderNum = ShipDtl.OrderNum
And
OrderDtl.OrderLine = ShipDtl.OrderLine

inner join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
And
ShipDtl.PackNum = ShipHead.PackNum

where (OrderDtl.OpenLine = false)
group by ROLLUP( Year,WeekNum )

Hi Bruce,
could you elaborate more about the logic behind your BAQ by giving an example, i can see from your SQL code that your are calling data from 4 different tables ShipHead, ShipDtl, OrderHead, and OrderDtl

Al, thanks for your response. Essentially, this BAQ is consolidating by weeknum, the total number of shipments that happened 1-2 days after the order was placed. For instance, there were 407 shipments made during the week 38. Of those 326 shipped within 1 day and 201 of those shipped the same day. So our 1 day shipment goal was met for 81% of the shipments. Same day shipments were made 49% of the time.

Ultimately, I’d like those percentages to show in the dashboard. However, the system tries to calculate the percentage while aggregating and comes out 0% each time. I tried making the aggregate query a subquery to the company table, pulling up the aggregated values, determining percentages. Didn’t work. What I need to know is if there is a way (eg Crystal “AfterPrintingRecords”) to control the timing of the percentage calculation.

Add one more level of subquery but up. Make your current Top Level an inner subquery then create a new Top Level subquery and include just the subquery. Now everything is calculated and you create calculated fields for your percentages on the new top level.

Mark W.

2 Likes

I know this is a couple years old but I am running into this same issue. Adding another top level subquery still is returning zero. I am counting the number of pack slips in a date range vs the number of pack slips where a custom checkbox has been checked upon receipt. The numbers are returning fine but need to get the percentage. Did the additional subquery work for you Bruce?