Skip to main content
var openSalesOrders = SelectFrom<SOLine>
    .InnerJoin<SOOrder>.On<SOLine.orderNbr.IsEqual<SOOrder.orderNbr>
        .And<SOOrder.orderType.IsEqual<SOLine.orderType>>>
    .InnerJoin<InventoryItem>.On<SOLine.inventoryID.IsEqual<InventoryItem.inventoryID>>
    .InnerJoin<INItemClass>.On<InventoryItem.itemClassID.IsEqual<INItemClass.itemClassID>>
    .Where<INItemClass.itemClassCD.IsEqual<@P.AsString>
        .And<SOLine.openQty.IsGreater<Zero>>
        .And<SOLine.operation.IsEqual<@P.AsString>>>
    .View.Select(this, "811", "I");

Is there a way to add a GroupBy to the above query to group and sum all sales order lines by Month & Year?

Coming from Python and Django in particular, something similar to Trunc is what I am looking to do.

Does BQL offer anything similar?

Hi @manley75,

You need use AggregateTo along with the GroupBy clause.

Following documentation has the explanation and some examples that you can refer to,

Good Luck,

Hi @manley75,

You need use AggregateTo along with the GroupBy clause.

Following documentation has the explanation and some examples that you can refer to,

Good Luck,

 

I know about and have used the .AggregateTo<GroupBy<>> in my queries before but I have not found a way to have it group by month & year. If I .AggregateTo<GroupBy<SOLine.shipDate» it will group by the day, not the month the day is in.

Basically if the shipDate was 10/18/23 I would want it to group it along with every other SOLine in October and give it a new consolidated date of 10/01/23.

Hi @manley75 were you able to find a solution? Thank you!

You can try this in you group by  

Aggregate<GroupBy<DatePart<DatePart.month, DAC_DATE_FIELD>>>

 

Reply


Terms & ConditionsCookie settings