Status is a field that I have calculated based on the Couse Name. The values in that field are 1 and 0. Basically what I have done to calculate this is search if a course has completed Phase 1, put 1 then 0. If a student has completed Phase 2, put 1 then 0. Same for phase 3 and phase 4.
-Phase 1
-Phase 2
- Phase 3
- Phase 4
I will always have those 4 states that I have created. In my design I want to create a pivot table that looks like this. I want to replace those 1 and 0s by coloring the cell of the table. The objective of this presentation is to let the user know who are the 0s (the ones missing training)
StudentId
Course
Phase 1
Phase 2
Phase 3
Phase 4
1
A
1
1
0
0
1
B
1
1
1
1
2
A
1
0
0
0
2
B
1
1
1
0
The problem that I have is that when I select a student that does not have 1s in all their phases, the dimension is gone. For example, if I select StudentI=1 and Course=A, I lose Phase 3 and Phase 4.
StudentId
Course
Phase 1
Phase 2
1
A
1
1
This is not a matter of populating z,eros. It is because for that specific record Phase 3 and Phase 4 status do not exist in the data, how do I achieve to see the records when data is missing?
I would like to achieve this from the script and really trying to avoid any calculated dimensions.
