Combining Expressions VALUE_OF and IF

Combining Expressions - VALUE_OF() and IF()

In other guides we looked at how to use an IF() expression as well as how to use a VALUE_OF() expression to accumulate data with null values. In this guide, we will combine these expressions to tally the data in a Postcode Area and depending on if this value is over a set figure, return one of two values we determine within the expression.

In this example, if we know that sales of about £10,000 per Postcode Area is our breakeven point; we can use the VALUE_OF() expression to accumulate the sales for each Postcode Sector and then use an IF() expression to identify which areas are over or underperforming. The IF() expression will add a new column to the Postcode Area browse, providing a simple 'Yes'/'No' answer.

NOTE: Here we use 'Yes'/'No' but this could be customised to any desired string

• An IF() expression has the following structure:

• IF(A1, A2, A3)

• A1 = This is where the test definition, in our scenario sales greater than 10,000

• A2 = This is the value returned if the test is true

• A3 = This is the value returned if the test is false

• The VALUE_OF() expression is placed around the accumulated (integer or double) data, here this would be column displaying your sales data within the A1 argument:

IF(VALUE_OF(POST3_SECT:BRANCHCUSTOMERS:(MASTER.SPEND))>10000, 'Yes', 'No') as "Over £10K Sales”

NOTE: Text within an expression needs to be enclosed by apostrophes to designate this as a string.

The column “Over £10K Sales” in the screen shot below is the final result.