Problems with loads and managing new records added to Qlik Sense chart using Chart Level Scripting...

[diagonjope]

Greetings!

I am trying to use Chart Level Scripting to calculate the Total values for a table chart, because the expression needed to calculate the Totals is different from the expression used the body of the chart.

In order to attempt this, the 'Totals' option in the Appearance -> Presentation section of the chart has been disabled, and I am using an 'add load' statement in to inject a new line in the chart containing the values needed for the calculation of the totals (a simulated Totals line).

I will spare you from the details of how the intermediate and total values are created using variables, because that information is available in the Community post showing how to use CLS.  However, the lines below show how the new Totals line is created, and the image after that shows how the record injected appears in the chart after running the CLS script:

Add Load '0' as PNC,
        'Totales' as FN,
        num($(vC), '#,##0.00') as [V_C],
        num($(vA), '#,##0.00') as [V_A],
        num($(vP), '#,##0.00') as [V_P],
        num($(vM), '#,##0.00') as [V_M],
        num($(vMT), '#,##0.00') as [V_MT],
        num($(vC)/$(vMT), '##0.00%') as [MS_C],
        num($(vA)/$(vMT), '##0.00%') as [MS_A],
        num($(vP)/$(vMT), '##0.00%') as [MS_P],
        num($(vM)/$(vMT), '##0.00%') as [MS_M]
Autogenerate 1;

The results shown in the image are correct, but the dummy Totals line always appears at the end of the chart, and the user has to navigate all the way to the end of the chart in order to see the Total values. I have tried changing the sort order through the UI, or searching for the string 'Totales' in order to bring the line to the top of the chart, but none of these attempts have worked:it seems that the mechanisms that the QSE UI makes available to end users to sort columns or search for values do not work for records injected through CLS. 

In view of this behavior, I decided to add a "Replace load" of the resident HC1 table (this is the table name used in CLS to refer to the hypercube in the chart), and sort the table ascending based on the first field, so that the record injected could become the first in the table.  The statements below show the "Replace load" used, and the image after it shows the error I am getting when adding this load after the "Add load" shown above:

Replace load PNC,
    FN,
    [V_C],
    [V_A],
    [V_P],
    [V_M],
    [V_MT],
    [MS_C],
    [MS_A],
    [MS_P],
    [MS_M]
resident HC1 order by PNC asc;

Now, I am not attempting a "load * resident HC1", so this might be a bug.  In any case, eliminating the "order by" did not make any difference.

So, if anyone (@Michael_Tarallo?) knows how to move the injected "Totals" line to the top of the chart or how to solve the "Replace load" problem in Chart Level Scripting, I would appreciate your feedback.  Please tag @diagonjope so that I get notified when someone provides feedback.

Cheers,

++José

[rwunderlich]

@diagonjope 

Your question re CLS aside, I generally find I can use a normal measure to handle the requirement for a different total expression using if( Dimensionality() .. like this: 

if(Dimensionality() = 0
  ,Avg(Sales) // Total Row
  ,Sum(Sales) // Other rows
)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[diagonjope]

Thank you @rwunderlich for your suggestion!  I followed your advice and solved the problem using dimensionality() - I thought it only worked on pivot tables. 

Cheers,

++José