QlikView Debugging Tips

Debugging: QlikView Set Analysis or Expression Does not Work

 QlikView script has a debug mode, which is useful for debugging scripts.  However, many calculations in QlikView applications have to be made on the front end using set analysis and expressions.   And, variables are often used in set analysis and expressions to make codes more manageable and easier to maintain.    Oddly, there is no debugging tool available from QlikView for these front end development needs.   You could spend hours to debug and end up finding out some really small stuff that is hard to catch just by looking at it.     

I have constantly relied on two ways to meet these debugging needs:  One way is to make the expression available in a Text Box object so that you can easily inspect the resulting value from the expression.   The other way is to pass the expression to a variable.  Then, inset a new Input Box object to display the variable.    When testing an expression containing variables, both may be needed.

Let’s take year over year growth and trend as examples.  They are commonly required calculations in charts and/or tables for many BI reports.   In QlikView, growth has to be calculated on the front end of the application instead in the script.   Those calculations can be complicated to debug in charts. Ideally, the expressions should be tested before used in a chart.

Below are three simple steps for debugging expressions using sheet objects available in QlikView front end applications:  

The first set analysis below calculates current year total sales for account type 100 during the period(s) selected by the report user.   The second set analysis calculates the total sales for the same account type during the same period a year prior to the user selected year.  The third calculation is an expression for total sales year over year growth (rate) for that account type during the period(s) the report user selected.   Period(s) in QlikViewcan be consecutive or non-consecutive days, weeks or months or whatever time period made available.


1) Total Sales (Selected Year):  

SUM({<Year={'$(vMaxYear)'}, AmountType={'100'}>} [Sales Amount])  

2     2) Total Sales (Prior Year Same Period):   

Round (SUM({<Year={'$(vPriorYear)'}, AmountType={'100'}>} [Sales Amount] * vBDays/vBDays_P)+0.0000001 , 0.01)

3   3)  Sales Growth Same Period YOY (=Total Sales (Selected Year)/ Total Sales (Prior Year Same Period) -1):

Num ( SUM({<Year={'$(vMaxYear)'}, AmountType={'100'}>} [Sales Amount])  / Round (SUM({<Year={'$(vPriorYear)'}, AmountType={'100'}>} [Sales Amount] * vBDays/vBDays_P)+0.0000001 , 0.01) -1,'#,##0.00%; -#,##0.00%', '.',','))

In the Sales Trend chart, it is difficult to debug if the expression somehow is not working.   To debug, simply follow the three steps below:

Step 1.  Pass the Total Sales set analysis to a variable as below:

Total Sales (Selected Year):  

vSales = SUM({<Year={'$(vMaxYear)'}, AmountType={'100'}>} [Sales Amount]) 

Total Sales (Prior Year Same Period):   

vSales _PriorYear = Round (SUM({<Year={'$(vPriorYear)'}, AmountType={'100'}>} [Sales Amount] * vBDays/vBDays_P)+0.0000001 , 0.01)

With above, Sales Growth Same Period YOY expression can be simplified as:
 
 Num ($(vSales)/fabs ($(vSales_PriorYear))-1,'#,##0.00%; -#,##0.00%', '.',',')) 

Step 2.  Insert an input box and add the two variables under “Displayed Variables” in the General tab of the Input Box properties window.  The Input Box object will then display the value of each variable as shown in figure 1. 

Fig. 1.  Sheet Object: Input Box

It is now easier to see if the set analysis had any error or if it resulted in the expected values.
Step 3.  To test expression for Sales Growth, insert a Text Box sheet object this time.  Enter the expression above for Sales Growth Same Period YOY in Expression Editor via the General tab of the Text Box Properties window (see figure 2 below).
 
            Fig 2.  Text Box expression editor

Once you clicked OK, the calculation result of the expression will be displayed in the text box (see figure 3).   This can be easily verified to be correct as 7120145/6559079-1= 8.55%

                                                 Fig 3.  Sheet Object:  Text Box

Have fun trying out next time you need to debug expressions.  If you have any other way to debug the front end expressions in Qlikview, please share with your comments.

Labels: , ,