Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. READ MORE

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

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Andre_Sostizzo
Digital Support
Digital Support

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

Last Update:

May 27, 2021 7:17:09 AM

Updated By:

Sonja_Bauernfeind

Created date:

Sep 15, 2017 6:40:56 AM

Attachments

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 

wrong results.png

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.

presentation.png

Labels (1)
Contributors
Version history
Last update:
‎2021-05-27 07:17 AM
Updated by: