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