Sum all sales of new customer by months

[simonsiplak]

Hi guys,

I need little help with one of my scripts.  Below, I have script which can "flag" first invoice of customer based on date. But I also need to "flag" all sales of this customer in month of his first invoice. For example his first invoice was in Jan 2019 but he also had other invoices in Jan 2019. I need to flag all of his invoices in that month.  Column FirstMonths_Invoices_flag in picture.

Invoice_items:
LOAD
    Invoice_item_invoice_id,
    Invoice_item_generated_datetime,
    Invoice_item_user_id,
    Invoice_item_price
FROM [lib://InvoiceItem_D.QVD] (qvd);

Invoice_FirstOccurrence_TMP:
LOAD
    Invoice_item_user_id,
    min(Invoice_item_generated_datetime) as MinInvoice
    Resident Invoice_items
Group By Invoice_item_user_id;
left join(Invoice_items)
LOAD
    Invoice_item_user_id,
    MinInvoice as Invoice_item_generated_datetime,
    'FirstInvoice' as Invoice_user_first_invoice_attribute
Resident Invoice_FirstOccurrence_TMP;

Output should be:

[MynhardtBurger · Accepted solution]

Try the following:

Invoice_items_RAW:
LOAD
    Invoice_item_invoice_id,
    Invoice_item_generated_datetime,
    Year(Invoice_item_generated_datetime)*100 + Month(Invoice_item_generated_datetime) as Invoice_item_YearMonth,
    Invoice_item_user_id,
    Invoice_item_price
FROM [lib://InvoiceItem_D.QVD] (qvd);

Invoice_Items:
NoConcatenate
Load
    Invoice_item_invoice_id,
    Invoice_item_generated_datetime,
    Invoice_item_YearMonth,
    Invoice_item_user_id,
    Invoice_item_price,
    if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as First_Invoice_Flag
Resident Invoice_items_RAW
Order by Invoice_item_user_id, Invoice_item_generated_datetime, Invoice_item_invoice_id;

Left Join(Invoice_Items)
Load Distinct
    Invoice_item_user_id,
    Invoice_item_YearMonth,
    if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as FirstMonths_Invoices_flag
Resident Invoice_items_RAW
Order by Invoice_item_user_id, Invoice_item_YearMonth;

Drop Table Invoice_items_RAW;

[simonsiplak]

It's working like I want. "Order by" is simple but better solution than "Group by" IMHO. Thank you @MynhardtBurger.