Distinct Customer on rolling basis

[Anonymous]

Hi,

I am new to Qlik community and have been learning a lot here. Thanks!

I have a question and it could be easy thing. I am prby missing something.

I have a data set with customerID and transaction date. I wanted to see how many new/unique customers are used on monthly basis but also update the data set as next month approaches.

For example, if from Jan to March we have 100 customers, in April we used 50 customers but 35 of them were already in Jan-Mar. So only 15 'new' customers are added.

In May, now the customer list should show 115 total customers.

Month      TotalExistingCust     ExistingCustUsed     NewCust     TotalCust

Jan          50                              30                              10                   60

Feb          60                              40                              15                   75

Mar          75                              65                              25                  100

Apr          100                             80                              15                  115

May         115                             85                              5                    120

Thanks!

[Anonymous]

script or frontend solution?

[Anonymous]

Script please.

[Anonymous]

here's a basic approach for getting the number of new distinct customers per YearMonth:

(the rest should be no problem at all...)

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

UNQUALIFY *;

TEMP:
LOAD * INLINE [
Datefield, Supplier
07.03.2017, A
01.01.2017, A
04.01.2017, A
08.01.2017, A
09.03.2017, G
18.01.2017, A
19.01.2017, B
25.01.2017, A
31.01.2017, C
21.02.2017, A
21.02.2017, X
10.03.2017, F
11.03.2017, E
];

NoConcatenate

TEMP2:
LOAD ##,YM,Datefield, Supplier as Supplier_New ,
if(not Exists(Supplier_New,Supplier),
//new supplier
     if(YM<>Peek('YM'),
//new YearMonth
     1
//no new Yearmonth
     ,
Rangesum(Peek('CountNew'),1)
)
,
//no new supplier
     if(YM<>Peek('YM'),
//new YearMonth
     0,
//no new YearMonth
     Peek('CountNew'))
) as CountNew;
LOAD RecNo() as ##, MonthName(Datefield) as YM,* Resident TEMP
Order by Datefield,Supplier;

FINAL:
LOAD YM,max(CountNew) as CountNewSupliers
Resident TEMP2 Group by YM;

DROP Table TEMP,TEMP2;

[Anonymous]

Thank you Robin. Appreciate your help. I will try this and see if it works. Looks like it should.