Pivot chart expression

[#1]

Hi,

Consider the following scenario:

1. Candidate can solicitate to one or more companies

2. Each company has its own set of compliancies (criterias) the candidate needs to be checked upon.

Company	CompliancyID	Compliancy
A	1	Has ID
A	2	Has Bachellor degree
A	3	IQ score higher than 100
A	4	No criminal record
B	4	No criminal record

Fact table would look something like this

CandidateId	Company	CompliancyID	Compliant Date
1	A	1	15-5-2012
1	A	2	27-5-2012
1	A	3	31-5-2012
1	A	4	15-6-2012
2	A	1	27-6-2012
3	B	4	31-8-2012

the challenge:

show in Pivot chart, per candidate, show all compliancies with  a V mark if the candidate was compliant and X if not

if date 1-9-2012 was selected:

CandidateId	Company	Has ID	Has Bachellor degree	IQ score higher than 100	No criminal record
1	A	v	v	v	v
2	A	v	x	x	x
3	B				v

if date 31-5-2012 was selected:

CandidateId	Company	Has ID	Has Bachellor degree	IQ score higher than 100	No criminal record
1	A	v	v	v	x
2	A	x	x	x	x
3	B				x

[RedSky001]

The attached qvw seems to do exactly what you have proposed it should do in the two senarios.

Am I missing something?

Mark

[#3]

Almost. If you select Kate you will see only one compliancy. I would like to see here ALL compliancies which are bounded to Google.

[#4 · Accepted solution]

solved it in the script, by creating an empty record for each non-filled compliancy. Ugly, but works.