New Customer in Qliksense

[marishnagendran]

Dear All,

Invoice_Date	Customer_Name	Product	Qty
01-Apr-19	A	Product 1	30
02-Apr-19	B	Product 1	20
02-Apr-19	A	Product 1	20
01-May-19	A	Product 1	10
01-May-19	C	Product 1	30
01-May-19	D	Product 2	20
02-May-19	B	Product 2	10
01-Jun-19	A	Product 2	5
01-Jun-19	B	Product 2	10
01-Jun-19	E	Product 2	10

The above table is my transaction table

I want to find If the customer Name "A" billed in Apr Month of 2019 then i want to tag that customer as Apr-19 and the customer do his business on May,Jun,Jul,Etc then i want to tag that customer as Apr-19 Only.Like wise all the customer i want to do.

Kindly see below example

Invoice_Date	Customer_Name	Product	Qty	New Customer Tag
01-Apr-19	A	Product 1	30	Apr-19
02-Apr-19	B	Product 1	20	Apr-19
02-Apr-19	A	Product 1	20	Apr-19
01-May-19	A	Product 1	10	Apr-19
01-May-19	C	Product 1	30	May-19
01-May-19	D	Product 2	20	May-19
02-May-19	B	Product 2	10	Apr-19
01-Jun-19	A	Product 2	5	Apr-19
01-Jun-19	B	Product 2	10	Apr-19
01-Jun-19	E	Product 2	10	Jun-19

New customer Tag column i want to build in Transformation layer is it possible?

[colinh · Accepted solution]

Assuming your table is called "Invoices" then:

NewCustomerTag:
load Customer_Name,
monthname(min(Invoice_Date)) as [New Customer Tag]
Resident Invoices group by Customer_Name;

You can just leave that table separate, or if you want to join it onto Invoices as you have in your example then:

left join (Invoices)
load * Resident NewCustomerTag;

drop Table NewCustomerTag;

or you could left join to Customers if you have a Customers table.

[marishnagendran]

Thanks colinh it's working.

[marishnagendran]

I have another doubt

If it is current financial year then i want separate a new customer as  Apr-19,May-19 like wise.

If it is previous year i want tag the customer only year (e.g) 2017 or 2018

[colinh]

I don't know what your financial year is, but something like

NewCustomerTag:

if(year([New Customer Tag]=year(today()), [New Customer Tag], year([New Customer Tag]) as [New Customer Tag];
load Customer_Name,
monthname(min(Invoice_Date)) as [New Customer Tag]
Resident Invoices group by Customer_Name;

should work for calendar year.

[marishnagendran]

Our financial year is Apr to Mar