topic Re: Calculate new customers on front end in QlikView

Calculate new customers on front end

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

Hi,

I need to count no. of new customers i.e the customers that came to me for the first time ever, with respect to year and month.

This could be done in script easily but due to some business restrictions (need to calculate new customers with respect to current selections of Brand, Supplier etc.) I need to do this on the front end.

Attached is a subset of the data. Any help is appreciated.

Thanks

Re: Calculate new customers on front end

sushil353 — Wed, 16 Nov 2016 11:17:08 GMT

Hi,

at backend you can simply calculate a flag for custid like in which month the customer is new.. once that is done then use the flag and date range in set analysis to get the desired count.

Re: Calculate new customers on front end

Wed, 16 Nov 2016 11:22:46 GMT

Hi ,

It can be acieved at front end using below expression :

=Count(Distinct{<[Customer Id] = {"=count(Distinct Date)=1"}>}[Customer Id])

Its like count of Distinct [Customer Id], against which there exists a single distinct Date

Regards,

Srashti

Re: Calculate new customers on front end

jyothish8807 — Wed, 16 Nov 2016 11:28:06 GMT

Hi Wrvs,

What is the expected output ? What do you mean by "first time ever" ?

Re: Calculate new customers on front end

Anonymous — Wed, 16 Nov 2016 11:29:04 GMT

I can't do it in script. Need to do it on front end because the definition of a new customer changes dynamically.

Eg:

If the user selects brand (not present in the dataset I provided), then he would see all customers that bought that brand for the first time.

If the user selects item, then he would see all customers that bought that Item for the first time.

Similarly, there are numerous dimensions and their combination:

Eg:

Brand B1 has three itsm I1,I2,I3

A customer bought I1 and I2 for the first time in Jan and bought I3 in Feb. Now if I select this brand through a list box, I will count the customer as new in Jan only. But if I select I3 then he would be new in Feb not Jan.

I hope you get the logic

Re: Calculate new customers on front end

Anonymous — Wed, 16 Nov 2016 11:31:23 GMT

A customers who shopped for the first time in Feb 2016 but also in Apr 2016. So he would be counted as new customer in Feb but not in Apr.

Re: Calculate new customers on front end

Anonymous — Wed, 16 Nov 2016 11:48:32 GMT

This did not work

Re: Calculate new customers on front end

ElizaF — Wed, 16 Nov 2016 12:00:33 GMT

Hello,

What you need, is Cohort analyse. See below some examples

https://community.qlikview.com/thread/212405

https://community.qlik.com/thread/212175

Regards,

Eliza

Re: Calculate new customers on front end

susovan — Wed, 16 Nov 2016 12:29:53 GMT

You can try this below expression,

=Count({<Month={'1'}>}[Customer Id])

Re: Calculate new customers on front end

shubham_singh — Thu, 12 Jan 2017 13:34:28 GMT

I made a monthserial in script like this

(Year*12)+Month as MonthSerial.

Now I made a table with dimension as Month Serial and Measure as below.

Count(

aggr(

min(MonthSerial),

[Customer Id]

)

I got this output.

When I did cohort in Scrip I got this output.

This is a difference of 1 customer in Month 24193 but rest of the data matches.

It will also work on selection.

Please let me know if output is correct.