topic Distinct Customer on rolling basis in QlikView

Distinct Customer on rolling basis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Distinct Customer on rolling basis

Anonymous — Fri, 09 Jun 2017 15:30:30 GMT

script or frontend solution?

Re: Distinct Customer on rolling basis

Anonymous — Fri, 09 Jun 2017 15:37:27 GMT

Script please.

Re: Distinct Customer on rolling basis

Anonymous — Sun, 11 Jun 2017 11:59:26 GMT

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;

Re: Distinct Customer on rolling basis

Anonymous — Fri, 16 Jun 2017 14:21:56 GMT

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