Customer New or Repeat..problem with montly selection
Hi everyone,
i've been working with Qlikview for about 1 month and i've a big problem in QlikView Customer Analysis.
The situation is:
i have only one table in .qvw in which every rows is an order done by a customer in a specific 'date_order'; by default every customer_id who has done an order has a relative column 'type_of_customers' = 'REPEAT'. The creation date of customer can be prior to 'date_order', and in the table are inserted as many rows as customers with 'creation_date' as 'date_order' and 'type_of_customers' = 'NEW'. Here is an example:
ORDER_ID
CUSTOMER_ID
DATE_ORDER
TYPE CUSTOMER
QUANTITY
10
100
2014/05/02
REPEAT
2
100
2014/05/01
NEW
11
101
2014/05/20
REPEAT
3
101
2013/01/01
NEW
When the user select the Month of May the 'NEW' state must have priority on 'REPEAT' state: so i need to visualize 1 NEW CUSTOMER, 1 REPEAT CUSTOMER and TOTAL QUANTITY = 3.
I have two important issue to fix:
I have a pie chart to represent '# of customers for type'. I create a variable to count the customers that are 'NEW' and 'REPEAT' in the time selected; this is the expression 'vNewCustomers =Count(distinct{<CUSTOMER_ID = {"=Count(Distinct TYPE_CUSTOMER)=2"}>}CUSTOMER_ID)'. In the chart expression I've added: if(TYPE_CUSTOMER='REPEAT', COUNT(DISTINCT CUSTOMER_ID)-(vNewCustomers) ,COUNT(DISTINCT CUSTOMER_ID)).
This solution is ok if the user doesn't click on a specific type of customer; in that case number of 'REPEAT' increases and it is wrong.
I have to sum the quantity only for customer 101 because the customer 100, in the time selected, is 'NEW'.
Someone can help me??
Thanks a lot.
Romina
Anyone can help me?
Please it's very important!!
Romina
