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).
