Hello - I am new to Qlik Sense and struggling to find the Right Data model to meet my needs.
I am trying to analyse data from student assessments.
I have a large Raw Table structured like this - Ratings can be any values between 0 and 3.
Student #
Asst #
Time
Q1 Rating
Q2 Rating
Q3 Rating
...
...
Q90 Rating
1234
5678
Baseline
0
1
2
3
1234
5689
Inter
1
2
3
2
1234
6754
Inter
0
1
3
2
1234
7684
Latest
0
1
2
1
...
I would like to be able to create dashboard where I can select the question(s) to analyse from a field pick list and then display a set of KPIS that would calculate the improvement made by students from both the baseline and from their worst score.
I have created a cross Table that looks like this in my loading script:
Student #
Asst #
Time
Question
Rating
1234
5678
Baseline
Q1
0
1234
5678
Baseline
Q2
1
....
1234
5678
Baseline
Q90
3
1234
NA
Worst
Q1
1
1234
NA
Worst
Q2
2
...
1234
NA
Worst
Q90
3
1234
7684
Latest
Q1
0
1234
7684
Latest
Q2
1
...
1234
7684
Latest
Q90
1
I would like to now in my dashboard to be able to select a question (eg Q1) [or in fact a number of questions] from a Question Field List and display the count of values that started off as 2 or 3 in Baseline or Worst assessment and became the 1 or 0 in the Latest Assessment.
I can't use Set Analysis as my baseline and latest assessments are separate records.
I tried to also create a big table that looks like this to be able to compare in a set both the baseline, worst and latest value for a specific question but i wonder if i can now in my KPI visualisation use a function to reference the field i would like to count depending on the QuestionName field I have selected. Is that possible ? I could not find any help on such a possibility ?
Student
Baseline.Q1
Baseline.Q2
....
Worst.Q1
Worst.Q2
...
Latest.Q1
Lastest.Q2
...
1234
0
1
1
2
0
1
...
If not possible, what are my option ? Thanks so much for any guidance !
