New Customers throughout the years

[#1]

Hi,

I'm trying to create a bar chart (stacked) to compare Customers behavior (new, kept and lost) through the time (Years, Months, etc).

Due to my dimension is "Year", I'm facing problems using Set Analysis, because it only performs the calculation one time per object not by row. I already have a "workaround" that give me the correct numbers, but I would like to know a better solution. This is my current expression:

=Pick(Match(Year,2011,2012,2013,2014,2015),

Count( {$<CustomerID=p({$<Year={2011},Sales={'>0'}>} CustomerID)*e({$<Year={2010},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2012},Sales={'>0'}>} CustomerID)*e({$<Year={2011},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2013},Sales={'>0'}>} CustomerID)*e({$<Year={2012},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2014},Sales={'>0'}>} CustomerID)*e({$<Year={2013},Sales={'>0'}>} CustomerID)>} distinct CustomerID),

Count( {$<CustomerID=p({$<Year={2015},Sales={'>0'}>} CustomerID)*e({$<Year={2014},Sales={'>0'}>} CustomerID)>} distinct  CustomerID)

)

Find my New Customers_demo.QVW example attached.

* I also tried using Alternate States and AsOf Tables, but I couldn't make them work.

** I'm not considering doing aggregations in the script at this moment because the data model is more complex, and I would like to make this analysis using other dimensions.

Regards,

[hvfalcao]

Hi Octavio,

I'm facing the same problem... Did you find any solution? Thanks

[jolivares]

I suggest you that inside the script make that calculation.

Load the table, then order by custumer, year en make a column status,

[jolivares]

I Took part of your data and look :

T1:

LOAD * INLINE [

    CustomerID, Year, Sales, Date

    1, 2011, 1, 1/1/2011

    1, 2012, 1, 1/1/2012

    1, 2013, 1, 1/1/2013

    1, 2014, 0, 1/1/2014

    1, 2015, 0, 1/1/2015

    2, 2011, 1, 1/1/2011

    2, 2012, 1, 1/1/2012

    2, 2013, 1, 1/1/2013

    2, 2014, 1, 1/1/2014

    2, 2015, 1, 1/1/2015

    3, 2011, 1, 1/1/2011

    3, 2012, 0, 1/1/2012

    3, 2013, 1, 1/1/2013

    3, 2014, 1, 1/1/2014

    3, 2015, 1, 1/1/2015

    4, 2011, 1, 1/1/2011

    4, 2012, 1, 1/1/2012

    4, 2013, 0, 1/1/2013

    4, 2014, 0, 1/1/2014

    4, 2015, 0, 1/1/2015

    5, 2011, 1, 1/1/2011

    5, 2012, 1, 1/1/2012

    5, 2013, 1, 1/1/2013

    5, 2014, 1, 1/1/2014

    5, 2015, 1, 1/1/2015

    6, 2011, 1, 1/1/2011

    6, 2012, 0, 1/1/2012

    6, 2013, 0, 1/1/2013

    6, 2014, 1, 1/1/2014

    6, 2015, 1, 1/1/2015

];

T2:

NoConcatenate

Load CustomerID, Year, Sales,

     If((Sales=1 and Year=2011) or (Sales=1 and Year>2011 and Previous(Sales)=0),1,0) as New,

     If((Sales=1 and Year>2011 and Previous(Sales)=1),1,0) as Kept,

     If(Sales=0 and Year>2011,1,If(Year=2011 and Sales=0,Null())) as Lost

Resident T1

Order By CustomerID, Year;

Drop Table T1;

[jolivares]

ups I forget that:

[hvfalcao]

Hi Juan,

This seems to be the right approach to me. The problem is that my data origin table is structured like this:

T1:

LOAD

     OrderID,

    CustomerID,

    year(Date) as Year,

    SalesAmt;

SQL SELECT

OrderID,

    CustomerID,

    Date,

    SalesAmt;

FROM Sales;

In this case, I don't have all years and sales =1 or =0 on each CustomerID like your script:

    1, 2011, 1, 1/1/2011

    1, 2012, 1, 1/1/2012

    1, 2013, 1, 1/1/2013

    1, 2014, 0, 1/1/2014

    1, 2015, 0, 1/1/2015

Do you have any suggestion?

Thank you!

[jolivares]

I see you have the SalesAmt in this case this de value 1 and you can to reconstruct the value... try to post a piece of your real date.

[hvfalcao]

The Sales Amt isn't the problem. The problem is that I don't have all YEARS for each client ID on my table.

CustomerID, Year, SalesAmt

1, 2010, 1

1, 2011, 1

1, 2012, 1

2, 2010, 1

2, 2011, 1

3, 2010, 1

3, 2011, 1

4, 2011, 1

4, 2012, 1

For your script work, the table data should have all CustomerID with all Years, and SalesAmt (1 or 0). In my table, there's just the years that the customer really bought. The solution I thinks is to load another table, with all years for each customerID, with 1 or 0 to the salesamt, like your data. The problem is that I don't know how to do that!

I've appreciated you help!

[jolivares]

In this case you have to aggregate this years, I recommend you have a alternate calendar to do that containing all the years, dates, etc.

[#10]

Hi, I agree with you, make the calculation in the script seems to be the best option.

The problem with this solution is that my real fact table has million of rows, and now the end user wants to have the comparison by month too, which means more inter-record calculations and to add all customers per month to do the comparison.

Henrique, you can do the following, to make sure that you have all Customers and all Years in the fact table to compare: (according to my example)

Sales_Temp:

LOAD Distinct CustomerID

FROM

Sales.qvd

(qvd) Where not isnull(CustomerID);

Join

LOAD Distinct Year

FROM

Sales.qvd

(qvd) Where not isnull(Year);

Left Join (Sales_Temp)

LOAD CustomerID,

     sum(Sales) as Sales,

     Year

FROM

Sales.qvd

(qvd)

Group By CustomerID,Year;

Sales:

LOAD CustomerID,

     Sales,

     if(Sales>0,1,0) as Sales?,

     Year

Resident Sales_Temp Order by CustomerID ,Year ASC;

DROP Table Sales_Temp;

Sales_History:

LOAD CustomerID,

     Sales,

     Sales?,

     Year,

     if(Peek(CustomerID) <> CustomerID and Sales? = 1, 1,

      if(Peek(CustomerID) = CustomerID and Peek(Sales?) = 0 and Sales? = 1,1, null())) as N,

  if(Peek(CustomerID) = CustomerID and Peek(Sales?) = 1 and Sales? = 1, 1, null()) as K,

  if(Peek(CustomerID) = CustomerID and Peek(Sales?) = 1 and Sales? = 0, 1, null()) as L

Resident Sales;

Thanks to gmoralesWit‌ who helped me with this approach.

Saludos,