How to filter nulls in a parameter

Question & Answer
, , , , ,
1

Hi all.

I have a parameter with a control. I select an event_code in the control, and this parameter gives me the previous_event_code I need, and NULLs. And I need a way to get rid of the nulls in a calculated field. The codes are always strings. ChatGPT gives me solutions but there is syntaxis errors as:

maxOver(ifelse({previous_event_code_param} IS NOT NULL, {previous_event_code_param}, NULL), [{event_code}]) but there is syntaxis errors.

The final goal is to add to a line chart the sales of the previous event. I have a dataset with the following fields: event_code VARCHAR, previous_event_code VARCHAR, quantity INT, weeks_left_to_event INT (dim for x axis).

And I am trying to create a parameter with the event_code value in the control called selectedeventcode,

a calculated field so I get the previous_event_code for that field with this called previous_event_code_param:
ifelse({event_code} = ${selectedeventcode}, {previous_event_code}, NULL)

and then I think I may need to filter the nulls in a new calculated field such as filtered_previous_event_code

and finally a calculated field to calculate the sales called previous_quantity with something like
ifelse({event_code} = {previous_event_code_param}, quantity, 0)

I think something is wrong as I get teh good value and NULLs in previous_event_code_param, but then zero in previous_quantity.

Any help appreciated.

Thanks,
Sergio.

2

Hi,

You can filter out the all the nulls at visual level. I hope this will work for you.

Regards,
Naveed Ali

1 Like
3

Hi Naveed,

Nope. I need a variable (a calculated field or something similar) where I can storage only the good value of that list where I can find a lot of (the same previous_code) and the majority of nulls. I just need one previous_code.

5

Hi @sergio, welcome to the QuickSight Community, hope this approach helps:

  1. previous_event_code_param:
ifelse({event_code} = ${selectedeventcode}, {previous_event_code}, NULL)
  1. filtered_previous_event_code:
coalesce({previous_event_code_param}, '')
  1. previous_quantity:
ifelse({event_code} = {filtered_previous_event_code}, {quantity}, 0)

If you still encounter issues, double-check the following:

  • Ensure that your parameter selectedeventcode is correctly defined and matches the values in your event_code field.
  • Verify that the coalesce function is effectively filtering out NULLs by examining the results in a simple table visualization first.
  • Use aggregation functions like sum or max if your calculated field needs to aggregate values across different rows.

These steps should help you filter out NULL values and calculate the previous event’s sales correctly for your line chart.

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena).

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!