Repeated Customers

[Ram83]

Hi,

How to write the Set expression for Repeated customers with Sales and Sales Return based on PhoneNo and DocDate.

For Example,

ObjType	DocEntry	DocNum	DocDate	CardCode	CardName	DocTotal	PhoneNo
13	104505	1910801554	06-05-19	C7010926055	DEAN	30000	7010926055
13	105400	1910801740	09-05-19	C7010926055	DEAN	30000	7010926055
14	3142	1910800044	09-05-19	C7010926055	DEAN	-30000	7010926055
13	106228	1910801941	13-05-19	C7010926055	DEAN	38000	7010926055
13	108133	1910802361	20-05-19	C7010926055	DEAN	30000	7010926055
13	103610	1910801370	02-05-19	C8608666640	KARTHI M OPPO	100	8608666640
13	105066	1910801663	07-05-19	C8608666640	KARTHI M OPPO	400	8608666640
13	108331	1910802409	21-05-19	C8608666640	KARTHI M OPPO	300	8608666640
13	108599	1910802462	22-05-19	C8608666640	KARTHI M OPPO	100	8608666640
13	103849	1910501617	03-05-19	C9442387303	ABDUL KALAMAZATH A	25000	9442387303
13	104267	1910501714	05-05-19	C9442387303	ABDUL KALAMAZATH A	34199	9442387303
13	104485	1910501765	06-05-19	C9442387303	ABDUL KALAMAZATH A	33999	9442387303
14	3117	1910500044	05-05-19	C9442387303	ABDUL KALAMAZATH A	-34999	9442387303
14	3121	1910500046	06-05-19	C9442387303	ABDUL KALAMAZATH A	-33999	9442387303

In above table,

ObjType 13 means Sales and 14 means SalesReturn.

DEAN Customer has visited 4 times with different dates. and he has return to one time. So, I want to except the results No.of visited times 3 and Last visited Sales (20-05-19) Rs.30000.

KARTHI Customer has visited 4 times with different dates. and he has no return . So, I want to except the results No.of visited times 4 and Last visited Sales (22-05-19) Rs.100.

ABDUL Customer has visited 3 times with different dates. and he has return  2 times. So, I want to except the results No.of visited times 1 and Last visited Sales (03-05-19) Rs.25000. But this Customer not consider the repeated customer.

At this time, I need

1. No.of visited times based on DocDate and PhoneNo.

2. 2 times visited customers total sales. Similarly 3 times and 4 times visited Customers Sales.

3. How to write Calculated Dimension for 2 times,3 times,4 times and Above 4 times sales.

So, Please help me set expressions the above scenario.

[Sue_Macaluso · Accepted solution]

@Ram83  Are you using QlikView or Qlik Sense? I would like to move this into the correct product forum. 

[Ram83]

Thank you for giving reply. I am using Qlik Sense. Please help me.