I have a table in Excel which I’m trying to reproduce in Qlik but I’m really struggling with how to do it.
At its core the table is a dynamic pivot table (needs to cope with new columns being added without warning) with a several columns on the right that carry out variations on the totals within the pivot table. I can create the pivot table or I can create a table that displays the extra columns but I can’t find a way to display everything in one table.
Example data
Name
Test
Score
John
A
75
John
B
90
John
C
65
Fred
A
80
Fred
C
87
Bill
A
67
Bill
B
45
Gary
B
99
Gary
C
40
Example Output
Row Labels
A
B
C
Max Score
Tests Taken
Test Passed
Bill
67
45
67
2
1
Fred
80
87
87
2
2
Gary
99
40
99
2
1
John
75
90
65
90
3
3
Rows = Name
Columns = Test
Values = Sum of Score
Max Score =MAX(B2:D2)
Tests Taken =COUNTIF(B2:D2,”>0”)
Tests Passed = COUNTIF(B2:D2,”>60”)
Can anyone advise the best way to do this. I’ve thought about doing the pivot table during data load so that I can display everything in a table but the only way I know to do that is to use SQL to create the pivot and I don’t believe I can do that dynamically (i.e. I’d need to know the columns I was trying to create in advance).
