Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simonsiplak
Contributor II
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
Contributor III
Contributor III

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
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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