topic Re: yearly new customers count and details in QlikView

yearly new customers count and details

dusasuman — Sat, 26 Sep 2015 08:39:26 GMT

Hi Community

Here I have attached the sample file. in that I need to show the count of new customers have done a sales on year basis.

I have mentioned detail requirement in file itself,

Customer done sales in 2015 and not done in 2014 or below then he is new customer.

Find the attachment

Please help me out.

--suman

Re: yearly new customers count and details

MK_QSL — Sat, 26 Sep 2015 10:05:54 GMT

Data:

LOAD * Inline

[

FiscalYear,customer,Sales

2015,e,5

2015,b,10

2015,w,5

2015,s,20

2015,p,5

2015,z,10

2014,a,5

2014,b,10

2014,c,5

2014,d,20

2014,e,0

2014,y,10,

2013,a,5

2013,v,10

2013,c,5

2013,k,20

2013,e,0

2013,x,10,

2012,a,5

2012,b,10

2012,c,5

2012,d,20

2012,m,5

2012,w,10,

2011,a,5

2011,b,10

2011,c,5

2011,d,20

2011,e,0

2011,v,10,

];

Left Join

Load FiscalYear,customer, If(customer = Peek(customer), 0,1) as Flag Resident Data

Order By customer, FiscalYear;

Now create a Chart

Dimension

FiscalYear

Expression

SUM(Flag)

Re: yearly new customers count and details

dusasuman — Sat, 26 Sep 2015 12:20:51 GMT

for 2014 count is only 1. but getting output as 2.. and the logic should compare with the sales=0, we should exclude that.. for ex. in above script the customer 'e' have 0 sales in all years except the year 2015. so customer 'e' a new cust in 2015. and even in 2011 he is not a new cust.

Re: yearly new customers count and details

dusasuman — Sat, 26 Sep 2015 12:27:01 GMT

will ''firstSortValue" function helps?

Re: yearly new customers count and details

dusasuman — Sat, 26 Sep 2015 12:35:05 GMT

2014 count should be 2. Please check once

Re: yearly new customers count and details

swuehl — Sat, 26 Sep 2015 13:10:04 GMT

I don't get 2 new customer for 2014, only 1. Which two customers do you see flagged?

If you want to remove zero sales values, just filter the input table:

Left Join

Load FiscalYear,customer, If(customer = Peek(customer), 0,1) as Flag Resident Data

Where Sales >0

Order By customer, FiscalYear;

Re: yearly new customers count and details

MK_QSL — Sat, 26 Sep 2015 14:29:59 GMT

Data:

LOAD * Inline

[

FiscalYear,customer,Sales

2015,e,5

2015,b,10

2015,w,5

2015,s,20

2015,p,5

2015,z,10

2014,a,5

2014,b,10

2014,c,5

2014,d,20

2014,e,0

2014,y,10,

2013,a,5

2013,v,10

2013,c,5

2013,k,20

2013,e,0

2013,x,10,

2012,a,5

2012,b,10

2012,c,5

2012,d,20

2012,m,5

2012,w,10,

2011,a,5

2011,b,10

2011,c,5

2011,d,20

2011,e,0

2011,v,10,

];

Left Join (Data)

Load FiscalYear,customer,SUM(Sales) as TotalSales

Resident Data

Group By FiscalYear,customer;

Left Join

Load FiscalYear,customer, If(customer = Peek(customer) , 0,1) as Flag Resident Data

Where TotalSales > 0

Order By customer, FiscalYear;

Re: yearly new customers count and details

dusasuman — Sun, 27 Sep 2015 18:44:44 GMT

1. I am seeing 2 counts flagged for 2014.

2. is the logic comparing with all below years when finding the new customers in current year. for each year it should compare with all below years of the year to which you are finding new customers.

Thanks for your help swehl and Manish..

Re: yearly new customers count and details

dusasuman — Sun, 27 Sep 2015 18:48:08 GMT

Re: yearly new customers count and details

swuehl — Sun, 27 Sep 2015 20:08:22 GMT

Could you upload your latest sample QVW?