Creating flag fields to avoid Sum of If and other inefficient expressions

The more efficient our expressions are, the more efficient the whole document will be – especially where those expressions are used frequently.

Many times, with a more complex expression, just thinking about what is happening might lead you to come up with a flag field that could be precalculated in the script.

Calculation of a flag field does add extra time to the load script. It can also increase the size of the data set. But they are a lot quicker on the front-end, and that makes the document more efficient for users.

Getting ready

Generate QVDs created in the Reducing the number of distinct values recipe.

How to do it…

Follow these steps to create flag fields to avoid Sum of If expression and other inefficient expressions:

1. Create a new QVW file in the same folder as QVDs and load the following script:

   Calendar:
   LOAD DateID, 
        Date, 
        If(Year2Date(Date, 0, 1, Today()), 1, 0) As YTDFlag,
        Year, 
        Month, ...