Coalesce (PQL)

Evaluates the list of values in the order supplied and returns the first non-empty value.

• Returned Output: Numeric
• Library: PQL \ Common \ Math
• Version: 2020.02.000
• Compatibility:
  • Can be combined with and other PQL function throughout the application.
  • It CANNOT be used with MDX or VBA functions. But it can be used on MDX-based content in other parts of the application.

Syntax

* Click on the function's arguments above for more details on the input values.

Comments

• The list of numbers can be typed in as a comma delimited list.
• If the method is used in other parts of the application, the input parameters can be changed
  • In Formulate, the inputs can be Data points, other functions or members
  • In the dynamic querying tools (like triggers, infographics and dynamic text), the inputs can be static values or other methods

Different Function types

• This function is like the SQL Coalesce function or the MDX CoalesceEmpty

Examples

This example returns the number 123, since the first 2 inputted values are null or empty.

Coalesce(null, null, 123)

Assuming there were no sales figures in Australia till 2016, this example returns the sales for Australia in 2016 skipping the null sales figures for 2015. Since 2016 has values, the 2017 figures are ignored.

Coalesce( ([Country].[Australia], [Measures].[Sales], [Dates].[2015]), ([Country].[Australia], [Measures].[Sales], [Dates].[2016]), ([Country].[Australia], [Measures].[Sales], [Dates].[2017]) )