Internal behavior change of before() function in Qlikview 12 and later

[Andre_Sostizzo]

Changes have been made to how the before() function operates in QlikView 12.

Environment:

QlikView 12 and 12.10
QlikView 12.20 (November 2017) and up

The before() function does not calculate properly in Qlikview 12 and later version if we suppress zero values from the chart properties > presentation tab.  This used to work in Qlikview 11.20.

Example:

1. Create new document in Qlikview desktop 12 and later version
2. Reload the document with following script:
 

tab:
LOAD * inline
[
Product ,Category ,Sales,Year
Salt ,White, 30,2014
Salt, ,,2015
Salt ,Red ,30,2014
Sugar ,White ,10,2015
Sugar ,Brown ,20,2015
Sugar ,,,2014
Wine ,White ,40,2015
Wine ,Brown ,30,2015
Wine ,Red ,10,2014
]

3. Create pivot table following dimensions and expressions.

Dimensions:    

1. Category
2. Product
3. Year

Expression:    

1. SUM(Sales) and label TotalSales
2. Before(TotalSales) and label Before

4. From Chart properties > Presentation tab, enable partial sums for Category and Product dimensions, enable 'allow pivoting', and enable 'always full expanded'
5. Change Year dimension from column to row as shown in the screenshot.
6. You could see the different results as highlighted in Table1 from below screenshot 

Please find attached QVW file for reference.

Cause:

This is an intentional change of behavior. In QlikView 12 the "suppress zero" setting is being fully applied in conjunction with Inter-record functions, whereas this was not the case in QlikView 11 (a deficiency in QV11 that was corrected in QV12). Previously, the suppression of zero and/or missing values was not taken into account with Inter-record functions, however this is fixed in QV12 and subsequently ported back to QV11 (late SR).

Resolution:

In order to achieve a similar (or "expected") result in QlikView 12, simply uncheck the "suppress zero-values" and all zero values become available (like in QV11). as shown in screenshot.