I have two tables for to keep track of store visit and store scheduled for visit as following:
Plan_Store:
Week Project Store Executor Plan_Store
12/10/2015 AA Store XY John Store XY
12/10/2015 AA Store TU Mel Store TU
12/10/2015 AA Store ET Phil Store ET
Concatenante
Visited_Store
Week Project Store Executor Status Visit_Store
12/10/2015 AA Store XY John Visited Store XY
12/10/2015 AA Store TU - Visited Store TU
I am using Chart Table to present Three Reports:
1. Store scheduled for Visit
2. Visited Store
3. Store scheduled but NOT Visit
Please advise:
1. What will be the best way to construct the data to produce the 3 reports e.g. using Concatenate? or Link Tables?
2. How do I create the selection box e.g. Project, and it will link directly to both table and show the Plan Store and Visit Store information? Do I keep the field name the same for both table and use e.g. 'Project'? or I have to rename the field?
3. To build the Chart Table, my dimension will be Week, Project, Executor, and Store will used in Expression as Count(Store), is that Correct? As if I put store under Dimension, the table shows no data.
4. If I want to use Executor from Plan_Store in table to present the Visited_Store data, how do I do that? Dimension in Visited Store are:
a. Week
b. Project
Expression is COUNT(IF(Status = 'Visited', Store), do I need to include Store as dimension?,
Since the Executor info in Visited_Store might not 100% correct, therefore I would like to use Executor from Plan_Store to be in Visited Store, do I include this in Expression = COUNT(if(Plan_Store = Visit_Store, Executor) it does not seem to work as the Executor came out as Null Value;
'
4. I think I am confused about whethter I need to concatenate the table or left join the table and also whether to rename key field for both table or keep them the same so that they will link up automatically by Qlikview,
5. Is it correct that for Store Scheudle Report, my expression should be COUNT(Plan_Store), Visited Report si Count(Visit_Store) and for Store that have been scheduled but not visit, what will be the best way of getting those store? using set analysis ?
