yearly new customers count and details

[dusasuman]

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

[MK_QSL]

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)

[dusasuman]

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.

[dusasuman]

will ''firstSortValue" function helps?

[dusasuman]

2014 count should be 2. Please check once

[swuehl]

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;

[MK_QSL]

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; 

[dusasuman]

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..

[dusasuman]

[swuehl]

Could you upload your latest sample QVW?