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: Debugging, QlikTech, Qlikview
