topic Re: Revisit calculation in QlikView

Revisit calculation

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

Dear all,

I want to calculate the count of customers who revisited again.In this case customerid 1 purchased 3 products on same date but on different time i dont want to calculate revisits in same day.Revisit should be based on different date.

My output should be

count of revisit is 2.(only customerid 1 and 2 revisited again)

PFA for app

customerid	Date	Product	revenue
1	1/1/2017	dfg	4000
1	1/1/2017	ass	3000
1	1/1/2017	rer	1000
2	23/1/2017	wes	6000
2	25/1/2017	tre	2000
3	4/2/2017	ewe	5000
4	5/2/2017	asa	7000
1	7/2/2017	adas	10000

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 09:56:50 GMT

For a solution which doesn't involve changes in the script... try this

=Count(DISTINCT Aggr(If(Count(TOTAL <customerid>DISTINCT customerid&Date) >= 2, customerid), customerid, Date))

If you can make changes in the script... then try to create a new field like this

AutoNumber(customerid&Date) as KeyField

and then something like this

=Count(DISTINCT {<KeyField = {"=Count(TOTAL <customerid>DISTINCT customerid&Date) >= 2"}>} customerid)

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 10:19:56 GMT

Dear Sunny,

Thank u for ur reply.

i have added another visit for customer id 2 when we select customer id 2 it gives visits as only 1 but it should be 2.

PFA for app.

customerid	Date	Product	revenue
1	1/1/2017	dfg	4000
1	1/1/2017	ass	3000
1	1/1/2017	rer	1000
2	23/1/2017	wes	6000
2	25/1/2017	tre	2000
3	4/2/2017	ewe	5000
4	5/2/2017	asa	7000
1	7/2/2017	adas	10000
2	26/1/2017	wer	2330

Regards,

Joshua. !

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 10:21:46 GMT

Dear Sunny,

I tried it in script also its not working.

Regards,

Joshua.

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 10:59:57 GMT

What is the logic to see 2? I mean if you have selected only one customer, shouldn't you be seeing 1?

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 11:26:51 GMT

Hi Sunny,

If i select one customer i want to see how many times he revisited based on date.In below customerid 1 revisited one time and customerid 2 revisited 2 times.

customerid	Date	Product	revenue
1	1/1/2017	dfg	4000
1	1/1/2017	ass	3000
1	1/1/2017	rer	1000
2	23/1/2017	wes	6000
2	25/1/2017	tre	2000
3	4/2/2017	ewe	5000
4	5/2/2017	asa	7000
1	7/2/2017	adas	10000
2	26/1/2017	wer	2330

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 11:31:56 GMT

Try this

=Count({<customerid *= {"*"}>}DISTINCT customerid&Date) - Count(DISTINCT customerid)

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 11:40:12 GMT

Dear Sunny ,

Thank you.

for calculating revisit% can we use this expression

=(Count({<HISNo *= {"*"}>}DISTINCT HISNo&Date) - Count(DISTINCT HISNo))

Count(DISTINCT HISNo)

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 11:41:17 GMT

What is the expected output based on the sample above when no selection is made and when 1 or 2 is selected?

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 11:44:54 GMT

Dear Sunny,

Yeah with no selections.

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 11:52:21 GMT

What is the expected output?

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 11:58:20 GMT

Hi Sunny,

In this case out of 9 customer visits there is 3 revisits (customerid 1 once and customerid 2 twice)

so (3/9)*100=33.33%

Re: Revisit calculation

sunny_talwar — Thu, 18 Jan 2018 12:03:02 GMT

This

Num((Count({<customerid *= {"*"}>}DISTINCT customerid&Date) - Count(DISTINCT customerid))/Count(customerid), '##.##%')

Re: Revisit calculation

ajsjoshua — Thu, 18 Jan 2018 12:10:08 GMT

Dear Sunny,

Thank You very much.

Regards,

Joshua.