Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

New Customer in Qliksense

Dear All,

Invoice_DateCustomer_NameProductQty
01-Apr-19AProduct 130
02-Apr-19BProduct 120
02-Apr-19AProduct 120
01-May-19AProduct 110
01-May-19CProduct 130
01-May-19DProduct 220
02-May-19BProduct 210
01-Jun-19AProduct 25
01-Jun-19BProduct 210
01-Jun-19EProduct 210


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_DateCustomer_NameProductQtyNew Customer Tag
01-Apr-19AProduct 130Apr-19
02-Apr-19BProduct 120Apr-19
02-Apr-19AProduct 120Apr-19
01-May-19AProduct 110Apr-19
01-May-19CProduct 130May-19
01-May-19DProduct 220May-19
02-May-19BProduct 210Apr-19
01-Jun-19AProduct 25Apr-19
01-Jun-19BProduct 210Apr-19
01-Jun-19EProduct 210Jun-19

 

 

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

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: New Customer in Qliksense

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.

 

 

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: New Customer in Qliksense

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.

 

 

View solution in original post

Highlighted
Creator
Creator

Re: New Customer in Qliksense

Thanks colinh it's working.

Highlighted
Creator
Creator

Re: New Customer in Qliksense

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

Highlighted
Partner
Partner

Re: New Customer in Qliksense

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.

Highlighted
Creator
Creator

Re: New Customer in Qliksense

Our financial year is Apr to Mar