Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Current vs. previous year

I have the following data.

   

YearCustomerAmount
2010ABC200000
2011ABC240000
2012ABC288000
2013ABC345600
2014ABC414720
2015ABC497664
2012BCD125000
2013BCD250000

I want the following:

a. loyal customers. i.e. the list of customers who purchase regularly in all the years

b. new customers -  when I select a year, I want the list of new customers added in that year and their amounts

c. Lost customers - Customers who didn't business forever. for eg, BCD didn't do business for 2014 and 2015.

9 Replies

Re: Current vs. previous year

Have a look on the last links in this posting: How to use - Master-Calendar and Date-Values.

- Marcus

MVP
MVP

Re: Current vs. previous year

Hi,

Try like this

a. loyal customers -

Dimension: Customer

Expression: Sum(Aggr(If(Count(Distinct Year) = Count({1} Year), 1, 0), Customer))


b. new customers

Dimension: Customer

Expression: Sum({<Customer=E({1-$} Customer)>} Amount)


c. Lost customers

Dimension: Customer

Expression: Aggr(If(Sum({<Year={'$(=Max(Year))', '$(=Max(Year) - 1)'}>} Amount) = 0, 'No Sales in Last two Years'), Customer)


Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Current vs. previous year

Jagan,

Thanks a lot.

It works fine.

but it compares only the previous year.

for eg I have  saes in 2011 to customer A and in 2014

Then it is classified as new customer in 2014. It compares with 2013 not for all the prior years.

Can you please help me in this.

MVP
MVP

Re: Current vs. previous year

PFA

for solution.

a. loyal customers -

Dimension: Customer

Expression: Sum(Aggr(If(Count(Distinct Year) = Count(DISTINCT TOTAL Year), 1, 0), Customer))

b. new customers

Dimension: Customer

Expression: Sum(Aggr(If(Count(DISTINCT Year) = 1 AND Count(TOTAL DISTINCT Year) = 1, Sum(Amount)), Customer))


c. Lost customers

Dimension: Customer

Expression: If(Len(Only({<Customer=E({1<Year={'$(=Max(Year))', '$(=Max(Year) - 1)'}>} Customer)>} Customer)) > 0, 1)


Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Current vs. previous year

sorry to trouble you again.

in the attachment, if there is sales in 2011 and 2013 but not in 2012, then it is considered as new customer in 2011 and 2013.

if I select 2014, it should show BCD as it is a lost customer, not only in 2014 but also in 2015.

Can you please help.

MVP
MVP

Re: Current vs. previous year

Try like this

New Customer: If(Sum({<Year={'$(=Max(Year))'}>} Amount) > 0 AND Sum({<Year={'$(=Max(Year)-1)'}>} Amount) = 0, Sum(Amount))

Lost Customer: If(Sum(Amount) =0 AND Sum({<Year=E(Year)>} Amount) > 0, 1)

Regards,

Jagan.

sinanozdemir
Valued Contributor III

Re: Current vs. previous year

Hi Kartik,

I am not sure about your business requirements, but here is my approach (I would like to handle this in the load script as much as possible):

Capture.PNG

In the front end, you can create a straight table and use the two new dimensions:

Capture.PNG

Capture.PNG

Here is the expression for Active/Lost Customer:

Capture.PNG

Hope this helps.

Thanks

MVP
MVP

Re: Current vs. previous year

Hi Kartik,

If you got the answer close this thread by giving Correct and useful answers.

Regards,

Jagan.

Arjunarao
Honored Contributor II

Re: Current vs. previous year

Hi Kartik,

If you got the answer close this thread by giving Correct and useful answers.

Community Browser