QlikView Your Business. Troyansky Oleg

time, the two fields are aggregated separately, and then one summarized result is subtracted from the other. The end result of the two expressions is virtually identical (there might be subtle differences that have to do with missing values, but you can ignore them at the moment).

      However, the following two expressions will produce completely different results, despite the similarities:

      versus

      The first expression calculates the average margin percent, and it’s accurate – the summarized Cost of Goods Sold (COGS) is subtracted from the summarized Sales Amount, and the result is divided by the summarized Sales Amount. Conversely, the second expression, while valid syntax-wise, is logically incorrect. The margin percent calculation is enclosed in a single aggregation function, therefore it is performed at every detail line, and then the resulting percentages are summarized. Even if you replaced sum() by avg(), the result would be different from the formula above, and most likely be different from the expected result.

      To summarize this brief introduction to chart expressions, let’s emphasize the two points that you’ve just seen:

      ● Chart expressions should always be defined with an aggregation function.

      ● When multiple fields are involved in a calculation, it’s important to determine the order of operations – which calculations need to happen at a detailed level and which calculations need to apply to the aggregated results.

      Chart Dimensions

      If charts always show aggregated data and chart expressions define what aggregation functions should be used, then dimensions describe the level of aggregation for the chart.