Calculated sales report

[MrBologna]

Hello,

I am trying to create a sales report form that shows monthly and yearly sales data, and I have 2 questions.

1. Can I group by date, but split that up between fields? See below screenshot

As you can see, I have these fields grouped separately by entry date and by shipment date. Is there any way I can combine the two, and just have 4 fields next to the month labels?

2. Can I do calculated fields in a report?

Our current method for seeing this information is an excel file, where we can just write the equation currentBacklog = lastMonthBacklog + thisMonthBookings - thisMonthShipments.


I can't calculate this in the query directly (as far as I know) because it won't have the grouped data (and I don't want it done on a per-entry basis)

I haven't been able to find a way to do this on my own besides using a referenced excel file.

[CJ_London]

1. I don't see why not. Assuming sales (bookings?) and shipments are two separate queries, join them together on year/month
2. yes, but not as you are showing it, use a subquery to sum bookings-shipments where year/month<=current year/month - assumes year/month is yyyymm (not the full month year you have at the moment)

pure guess how you are doing it at the moment but perhaps salesquery (Q1) is

SELECT format(Saledate,"yyyymm") as yrMth, Year(SaleDate) as Yr, format(SaleDate, "mmmm yyyy") as Mth, Sum(SaleQuant) as SaleQty, Sum(SaleValue) as SaleAmount
FROM tblSales
GROUP BY format(Saledate,"yyyymm"), Year(SaleDate) , format(SaleDate, "mmmm yyyy")

similar for the shipquery (Q2)

SELECT format(Shipdate,"yyyymm") as yrMth, Year(ShipDate) as Yr, format(ShipDate, "mmmm yyyy") as Mth, Sum(ShipQuant) as ShipQty, Sum(ShipValue) as ShipAmount
FROM tblShipping
GROUP BY format(Shipdate,"yyyymm"), Year(ShipDate) , format(ShipDate, "mmmm yyyy")

for 1 above (Q3)

SELECT Q1.yrmth, Q1.yr, Q1.mth, Q1.SaleQty, Q1.SaleAmt, Q2.ShipQty, Q2.ShipAmt
FROM Q1 INNER JOIN Q2 ON Q1.yrmth=Q2.yrmth

for 2 above to include the backlog base another query on Q3

SELECT A.yrmth, A.yr, A.mth, A.SaleQty, A.SaleAmt, A.ShipQty, A.ShipAmt, sum(B.SaleQty-B.ShipQty) as QtyBacklog, sum(B.SaleAmt-B.ShipAmt) as AmtBacklog
FROM Q3 A INNER JOIN Q3 B ON A.yrmth>=B.yrmth

note the modified join, create as normal in the query builder, then go into the sql window and change it.

alternative you can do in the query window is a cartesian query (no join), but will be slower for large datasets

SELECT A.yrmth, A.yr, A.mth, A.SaleQty, A.SaleAmt, A.ShipQty, A.ShipAmt, sum(B.SaleQty-B.ShipQty) as QtyBacklog, sum(B.SaleAmt-B.ShipAmt) as AmtBacklog
FROM Q3 A, Q3 B
WHERE A.yrmth>=B.yrmth