IFBLANK() is used to plug gaps and automate the completion of patchy data. Here are some ways to use IFBLANK() efficiently, and explanations of some key features.

Example 1: IFBLANK plugs gaps in Transaction Lists

IFBLANK() is often used to complete high-density Blocks like Transaction Lists. Imagine the company WLGY Lemonade imports sales data into a Transaction List called 'Trans list', but the dates are spread across two or more columns. You know that column 'Date col 1' is the most reliable, followed by 'Date col 2'. 

To compile one definitive dates column that prefers data from 'Date col 1', you make a third, 'Merging 2 cols', containing the following formula:

IFBLANK('Trans list'.'Date col 1','Trans list'.'Date col 2')

Example 2: Using IFBLANK efficiently in low-density Metrics

Large datasets are more performant if you maximize sparsity. Use of IFBLANK() can quickly increase your density if you’re not careful, with an undesirable impact on performance.

To keep IFBLANK() from overpopulating your dataset, you can combine IF() statements with IFBLANK().

For example, imagine WLGY Lemonade is giving performance-related bonuses to its employees. Those with qualifying appraisals have had their bonus manually input into the Metric already. The remaining Active Employees will get a bonus of 10. 

Instead of using just IFBLANK(Bonus, 10), which awards 10 to all employees in the current Metric, the modeler opts for IFBLANK(Bonus, IF('Active Employee',10)) to keep inactive employees’ Items BLANK. This increases sparsity.

Example 3: Implications of IFBLANK

WLGY Lemonade collects sales data in a sparse Metric called 'Wk sales data':

It has a Metric that aggregates the data using the REMOVE SUM modifier. It applies the following IFBLANK() expression in the formula, replacing blanks with ones:

IFBLANK('Wk sales data',1)

The output in the Metric is as follows: 

The output has no ones, because:

• IFBLANK() works at the granular level of its arguments, before modifiers are applied.
• It has replaced all blanks in the Metric with ones, finding two blanks per city, across five cities in most cases. 1 x 2 x 5 = 10.
• IFBLANK() retains the non-blank data, such as in week WC 2023-01-16, which has sales of 11 for product ‘Grenadine’ sold in city ‘Tartberry’. That week had nine blank cells, now filled with ones, plus one cell containing 11. This totals 20, as returned. And week WC 2023-01-30 had six blank cells filled with ones, plus sales of nine, totalling 15.
• The ones becomes visible if you remove the REMOVE SUM aggregation:

Learn more

• IFBLANK function
• IF function