Excluding Null Values in Script Sum & Multiplication expression

malradi88 — Sat, 16 Nov 2024 17:37:11 GMT

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

topic Excluding Null Values in Script Sum & Multiplication expression in App Development

Excluding Null Values in Script Sum & Multiplication expression