topic Re: # Of New Customer in QlikView

# Of New Customer

jood_ahmad — Tue, 19 Apr 2016 15:15:40 GMT

Dear Qlik Community ,

I know this topic it posted more than 3 time but I check all of it nothing similar to this.

My case is ; I need to get the new Customer where is in variable changed based on the variable to change .

I have to QVD one for Orders and the other for the customer data, the order qvd it contain all details for the order data with column called SHIFT_DATE this column store all date for the daily orders and also this column I use it as my master calendar and my primary key in the order view is my Cust_phone_number containing the customer phone number joined with the customer view, and inside the customer view there is a CREATION_DATE this will store the date for customer in the first time in my DB; so my scenario is :

If I need to know the new customer in specific period based in the selection to count the customer phone number where the creation date >= the current selection.
If I need to know the new customer by using the variable in input box once I enter the date that I need it will search only all new customer that > my input date.

I try to use variable and set analysis but every time gave me wrong data or errors in the set analysis,

Any help pleas on this.

I attached the QVD Sample containing the views.

Message was edited by: Ahmad Kastero

Re: # Of New Customer

Colin-Albert — Tue, 19 Apr 2016 15:21:56 GMT

It would help if you reduce the data in your example rather than posting a 70Mb file.

Preparing examples for Upload - Reduction and Data Scrambling

Re: # Of New Customer

jood_ahmad — Wed, 20 Apr 2016 07:06:13 GMT

Thank you Colin Albert for advice, i upload the reduced copy .

Re: # Of New Customer

kaushiknsolanki — Wed, 20 Apr 2016 07:22:17 GMT

Dear Ahmad,

Can you guide us more, like what selection you are doing and what should be the output as per your selection.

Regards,

Kaushik Solanki

Re: # Of New Customer

jood_ahmad — Wed, 20 Apr 2016 07:45:53 GMT

what i did in my original application on one of the things to get the new and old customer is :

1. i create a variable to get the new customer before 3 month the variable was =Max(CREATION_DATE)-90 to get 3 month back but when i validate the numbers with my database it was incorrect and it dose not work if i select specific month; what i want is if i select specific month it will retrieve the number of the new customer in text box (it will check if the CREATION_DATE >= Current selection or my variable )

Re: # Of New Customer

kaushiknsolanki — Wed, 20 Apr 2016 08:08:08 GMT

Ok. This clears what you want.

Now can you help in vallidating the result. Can you tell us what should be the count of customers for 3 months in the data which you had shared.

Regards,

Kaushik Solanki

Re: # Of New Customer

jood_ahmad — Wed, 20 Apr 2016 08:14:44 GMT

yes,

i just execute query on my DB for :

count the CUST_PHONE_NMBR where the CREATION_DATE >='2016-01-01' and CREATION_DATE <='2016-03-31'

and the result is 120113 ..

Re: # Of New Customer

kaushiknsolanki — Wed, 20 Apr 2016 08:20:52 GMT

The data which you have shared gives the count as 114458 and distinct count as 100890.

Make sure that you have extracted same numbers of records from database to qlikview.

Now only way to check what goes wrong or what records are missing is.

1. Check at Month level.

Get the monthly count.

2. Check which month you have mismatch.

3. Check the individual customer ID for which data is missing.(Using Vlookup)

Regards,

Kaushik Solanki

Re: # Of New Customer

jood_ahmad — Wed, 20 Apr 2016 08:28:15 GMT

i check on month level:

Jan = 42852

Feb = 37968

Mar = 39293

and the total is 120133

Re: # Of New Customer

kaushiknsolanki — Wed, 20 Apr 2016 08:31:53 GMT

Hi Ahmad,

Values do not match for any month.

Make sure that you have fetched same numbers of records in Qlikview from Sql Database.

Regards,

Kaushik Solanki

Re: # Of New Customer

jood_ahmad — Wed, 20 Apr 2016 08:58:59 GMT

Hi kaushik.solanki,

which date you use you should use the CREATION DATE i check the data that in the QV from SQL for the first 3month in both 2015 & 2016 it give me the same number because i count the CUST_PHONE_NMBR based on the creation date filter.

in 2015 :

DB QV

Jan = 32150 32149

Feb = 29243 29244

Mar = 35520 35521

it seem the same with little difference it because the sync from other database .