Our sales force system arranges companies into 'groups', and then lets us assign a project to one or more members of the group. Simply put, it looks like this
GROUP COMPANY PROJECT GR1 C1 P1 GR1 C1 P2 GR1 C2 P3 GR1 C2 P4 GR1 C3 P2 GR1 C3 P3 GR1 C3 P5 GR2 C4 P1 GR2 C4 P3 GR2 C5 P2 GR2 C5 P4 GR2 C5 P5 GR2 C6 P3 GR2 C6 P5 When I load this data from Excel into QV, and create a pivot table of Group, Company as dimensions, and Project as expression, I can't understand the results. For example, if I pick "P1" and "P3" from a 'Project' listbox, I'd expect to see: GROUP COMPANY PROJECT GR1 C1 P1 GR1 C2 P3 GR1 C3 P3 GR2 C4 P1 GR2 C4 P3 GR2 C6 P3 But company "C4" doesn't show up at all. And if I pick all five projects in the listbox, the pivot table is empty. What we want is a report that shows, for any given group, what companies are in that group, and then if you pick a project, we want to see what companies DO have that project assigned, and what companies DON'T have that product assigned. I'm so confused... please help!
The problem you are running into is that you cannot have the Project field as your expression unless it is, say, a count of projects or something like that. Instead, you need it as a dimension in order to give you the output you are looking for. However, a pivot table won't populate without an expression so we need to fool QlikView a bit. See attached. Note that with the pivot table we cannot hide the expression column, so you may prefer a straight table.
