Hi everyone,

I'm trying to create a bar chart forecast in IFS Lobbies that shows the total net amount of planned spares (SUM(UNIT_PRICE_INCL_TAX * QTY_INVOICED)) grouped by custom delivery periods rather than standard calendar months.

We have predefined customer order delivery months (e.g., "Jan-25", "Feb-25"), each covering specific date ranges rather than full months. I need to map planned delivery dates to these periods for proper grouping and display them on the X-axis of the chart.

Here’s the query structure I’m using:

-- Name: 
Customer Order Forecast Per Month

-- View: 
&AO.CUSTOMER_ORDER_LINE

-- Where:
EXISTS (
    SELECT 1 
    FROM (
        SELECT '01/01/2025' AS start_date, '01/01/2025' AS end_date, 'Year Opening Period 2025' AS period FROM DUAL
        UNION ALL SELECT '01/01/2025', '24/01/2025', 'Jan-25' FROM DUAL
        UNION ALL SELECT '25/01/2025', '21/02/2025', 'Feb-25' FROM DUAL
        UNION ALL SELECT '22/02/2025', '24/03/2025', 'Mar-25' FROM DUAL
        UNION ALL SELECT '25/03/2025', '23/04/2025', 'Apr-25' FROM DUAL
        UNION ALL SELECT '24/04/2025', '22/05/2025', 'May-25' FROM DUAL
        UNION ALL SELECT '23/05/2025', '23/06/2025', 'Jun-25' FROM DUAL
        UNION ALL SELECT '24/06/2025', '23/07/2025', 'Jul-25' FROM DUAL
        UNION ALL SELECT '24/07/2025', '21/08/2025', 'Aug-25' FROM DUAL
        UNION ALL SELECT '22/08/2025', '23/09/2025', 'Sep-25' FROM DUAL
        UNION ALL SELECT '24/09/2025', '24/10/2025', 'Oct-25' FROM DUAL
        UNION ALL SELECT '25/10/2025', '21/11/2025', 'Nov-25' FROM DUAL
        UNION ALL SELECT '22/11/2025', '31/12/2025', 'Dec-25' FROM DUAL
        UNION ALL SELECT '31/12/2025', '31/12/2025', 'Year Closing Period 2025' FROM DUAL
    ) delivery_periods
    WHERE planned_delivery_date BETWEEN TO_DATE(start_date, 'DD/MM/YYYY') AND TO_DATE(end_date, 'DD/MM/YYYY')
)
AND objstate != 'Cancelled' 
AND contract LIKE NVL(UPPER('$SITE$'),'%')
AND company LIKE NVL(UPPER('$COMPANY$'),'%')
AND customer_no LIKE NVL(UPPER('$CUSTOMER_NO$'),'%')
AND &AO.Customer_Order_API.Get_Authorize_Code(order_no) LIKE NVL(UPPER('$COORDINATOR$'),'%')

-- Order By:
TO_DATE(start_date, 'DD/MM/YYYY')

-- Group By:
period

-- Columns:
Count (order_no) -- Order Lines
Count (Distinct(order_no)) -- Orders
period -- Delivery Month
SUM(UNIT_PRICE_INCL_TAX * QTY_INVOICED) -- Total Net Amount
 

I am new to IFS and this style of development, but my knowledge of SQL is very good.

Any advice or insights would be greatly appreciated! Thanks in advance.