Excluding null values from calculations but not replacing them as 0

Question & Answer
,
1

I’m trying to make the below calculation to apply to various categories in the data. These calculated fields are aggregations from the data which contain scores from 0-10 as well as empty cells where a score wasn’t assigned.

({115 weighted}+{115b weighted }+{115d weighted}+{115e weighted}+{111 weighted})/100

when trying to execute this calculation some of the categories do not populate, I believe this may be because some categories have fields such as {111 weighted} that have no scores just null/empty cells which cannot be computed with the other fields that contain a score.

I cant change the null values to 0 as that would indicate 0 was given as a score when it was not. Is there a way of doing this calculation while somehow telling QuickSight to exclude the null values from the calculation?

Hope this makes sense, thank you!

2

Hi @trew1 - Welcome to AWS QuickSight community and thanks for posting the question. I understood you can not assign 0 to NULL values for those weighted fields, but when you are doing the final calculation, you have to tag to 0 for those fields are not available or develop a logic, if the fields value is not available then ignore from calculation.

Hi @Max @David_Wong - Any advise on this.

Regards - Sanjeeb

1 Like
3

Yes, it makes sense. In Quicksight, you can handle null or empty values in calculations by using conditional expressions. Specifically, you can use the ifelse function to check if a value is null and exclude it from the calculation.

Here’s an example of how you can modify your calculation to exclude null values:

({ifelse(isnull({115 weighted}), 0, {115 weighted}) +
  ifelse(isnull({115b weighted}), 0, {115b weighted}) +
  ifelse(isnull({115d weighted}), 0, {115d weighted}) +
  ifelse(isnull({115e weighted}), 0, {115e weighted}) +
  ifelse(isnull({111 weighted}), 0, {111 weighted})}) / 100

In this modified calculation, the ifelse function is used to check if each field ({115 weighted}, {115b weighted}, etc.) is null. If a field is null, it is replaced with 0. Otherwise, the original value is used. This ensures that null values are treated as 0 in the calculation.

By using this approach, you can perform the calculation while excluding null values and avoiding the misrepresentation of a 0 score when it was not assigned.

3 Likes