Testing the Aggr Function Used in an Expression

I’m often asked to take a look at someone's QlikView document with the explanation that “the expression doesn't work”. If it is a complex expression with multiple parts or multiple functions I suggest taking each part of the expression and testing it to make sure it is performing the expected function. When one of the parts is something simple like  sum(InvoiceAmt)  then most people get the idea that they can put that little part of the expression into a temporary chart column or text box and then, based on the current selections and/or chart dimension, check that the function is really showing the sum of the invoice amounts.

If the part of the expression that needs to be tested is an aggr function then you need to know how to check it because the aggr function does not return a single value like the sum function; it returns an array of one or more values. An array of multiple values will usually show up as a null in a chart expression or text expression. If the array happens to have only one value or if all of the values in the array are exactly the same then you will be able to see the result of the expression but its no guarantee that the function is working as intended.

In order to test the aggr function itself, I recommend surrounding the aggr function with a concat expression that will help you analyze the array of the values returned by aggr. For example, if you had a straight table chart with a dimension of geographic region, you might have an expression like this to calculate average sales rep invoice amount totals:
  Avg( Aggr( sum(InvoiceAmt), SalesRep) )

In order to view the values returned by the aggr function, you could create a temporary column and copy and paste the aggr portion of the original expression and surround it with a concat function that might look like this:
  Concat( Aggr( sum(invoiceamt), SalesRep), ' / ' )

That would create a text string showing the individual values in the array separated or delimited by ' / '
Another useful delimiter is ', ' which creates a text string showing the individual values separated by commas.

When using this technique with a chart column there might not be enough space to show all of the array values separated by the delimiter. If that happens, right-click on the chart cell you would like to examine and choose Copy to Clipboard - Cell Value (in a text box you may right-click and choose Copy to Clipboard - Text) and then paste the value into a text file or spreadsheet or other convenient place to review a long text string.

If the array of values you see using this method is what you expected then check out all of the other sections of your expression – if all of them are working as you expect then assembling them into a single expression should work in your application. I often recommend building and testing each of the parts as a development technique when building a complex expression.

One other method for examining the array of values returned by the aggr function is to use it in a temporary chart and use the aggr function as a calculated dimension. A calculated dimension is, by definition, an array of values and it may help you review the values. 

_______________ ___________________ ___________________

Note to Santhosh:   You can remove leading zeros from a numeric identifier like a SAP 18-digit material number in the loadscript with a line like this:
   replace(ltrim(replace(MATNR,'0',' ')),' ','0') as MATNR_NO_ZEROS,

If you foresee the need to do a left join load using the original identifier then keep it as a separate field. If you would like to turn this into a user-defined function like I wrote about three years ago (search on "qlikview maven user-defined function") then do this:

1. Create a variable named DropZeros
2. Paste this into the variable:  replace(ltrim(replace($1,'0',' ')),' ','0')
3. Now, you have something that works like a user-defined function. It could be used in your loadscript line like this:

   $(DropZeros(MATNR)) as MATNR_NO_ZEROS,

★★★