Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simonsiplak
New Contributor II

Sum all sales of new customer by months

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:
 
Screen Shot 2019-04-11 at 2.34.09 PM.png
1 Solution

Accepted Solutions
MynhardtBurger
New Contributor III

Re: Sum all sales of new customer by months

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;

View solution in original post

2 Replies
MynhardtBurger
New Contributor III

Re: Sum all sales of new customer by months

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;

View solution in original post

simonsiplak
New Contributor II

Re: Sum all sales of new customer by months

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