To Whom it May Concern,
I am currently having trouble with the following table:
Weightedassessment:
Load
distinct
sum (PreScore *Weight) as weightedscorepre,
sum (PostScore *Weight) as weightedscorepost,
sum (PostScore *Weight) - sum (PreScore *Weight) as weightedgradechange,
learnerassessment
Resident [Final_Assessment]
Group by learnerassessment, cohortassessment, [Academic Year], [Grade Level];
The purpose of the table is to generate correct pre & post scores according to grading-weight. At first it seemed like all the weighted scores were correct then I noticed that for students who do not have a pre-score in the excel sheet (null when viewed as a dimension in qlik) that zeros were turning up from the calculation (PreScore*Weight). This is skewing results both for the weightedscorepre & weightedgradechange fields.
Is there anyway of making Qlik interpret a numeric value * null as null ? Ideally I would like table to produce the following results if:
PreScore Weight Result
- .5 -
instead it is resulting in the following:
PreScore Weight Result
- .5 0
I tried a few different if statements in the table involving null functions but no luck (I can't exclude all zeros either as some prescores were actually zeros...also if i use a where clause in a preceding table it seems to delete the whole row for the individual not just his/her null prescore but also the postscore). Any advice would be much appreciated! Thank you for your support 🙂
Best,
Mohammed
