topic Re: How to select not assigned projects in QlikView

How to select not assigned projects

Tue, 05 Feb 2013 21:05:52 GMT

Hi folks,

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!

Re: How to select not assigned projects

Tue, 05 Feb 2013 21:10:49 GMT

Do you have a sample file? Your text is a little jumbled so I'm not sure what goes where.

Re: How to select not assigned projects

Tue, 05 Feb 2013 21:16:10 GMT

Man, I wish they had a decent guide on how to use this editor. I try and try to cut and paste tables, code, etc, and nothing ever comes out legible.

Attached the Excel data and the simple QV file.

Re: How to select not assigned projects

Tue, 05 Feb 2013 21:47:29 GMT

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.