Chart Level Scripting Use Case

[Vasujoshi_700]

Hi everyone,

Can Anyone provide me with the use case of char level scripting, i am not getting the idea how can i put chart level scripting in use, any suggestion, example or use case is welcome.

[rwunderlich]

Here is a use case I've come up with. Adding a Total bar to a bar chart. 

You would think this could be done with the chart script:

Add Load
'Total' as Division,
sum(Sales) as Sales
Resident HC1;

But that script actually replaces the first row, not what I expect. I'm unsure if that is a bug or a misunderstanding on my part. 

As a workaround, I can make it work with this script (this is how the first picture was generated)

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
  Let vTotal = vTotal + HCValue(Sales,J);
Next
Add Load 'Total' as Division, $(vTotal) as Sales Autogenerate 1;

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

[Vasujoshi_700]

Thanks sir,

so it means we can add new measures in particular char with the chart level scripting.

[rwunderlich]

Yes, I believe you can add new Measure columns. The documentation and examples are still rather scant. 

-Rob

[devan9876]

I don't have access to that version yet, but if I understand this feature properly I suspect it could be used for creating custom row subtotals like in a p&l statement which previously required some combination of extensions and/or making changes to the data model to load in dummy fields.

[Or]

Hi Rob, since you seem to have gotten this working a lot better than I have so far - have you had any luck adding data from a secondary source (not autogenerate/inline)? Documentation suggests we can use a following SQL statement, which would be brilliant as it would allow for real-time object updates from a database source, except I think that's just copy-pasted from the "regular" Load statement help page. I have not had any luck adding information from an SQL source or even from a resident table, though this might be an issue on my end given the lack of documentation and guesswork involved in getting the syntax right...

[Ratier]

I really can't understand why chart level script is not working for me. There's something i'm missing. I addapted your code for my exemple. Here's yours:

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotal = vTotal + HCValue(Sales,J);
Next
Add Load 'Total' as Division, $(vTotal) as Sales Autogenerate 1;

And since my Dimension is labeled "Mensal" and my Measure is labeled "Orçado", I changed this code to:

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotal = vTotal + HCValue(Orçado,J);
Next
Add Load 'Total' as "Mensal", $(vTotal) as Orçado Autogenerate 1;

The following error occur:

"Load statement could not find target column [1268] in hypercube"

If I change to "$(vTotal)" to "Floor($(vTotal))", the error disappears but the bar chart creates a empty bar.

What am I doing wrong?

[rwunderlich]

I think the 1268 may be the value of vTotal?  You could confirm by replacing vTotal in the Add load with a hardcoded number. Then try single quoting vTotal in the add load.

Add Load 'Total' as "Mensal", '$(vTotal)' as Orçado Autogenerate 1;

Maybe you have to double quote the measure name?

HcValue("Orçado", J)

and as "Orçado"

BTW, the bug in the first example I posted has been fixed. So you might try:

Add Load
'Total' as "Mensal",
sum("Orçado") as "Orçado",
Resident HC1;

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

[Ratier]

Tried

Let vTotal = 0;
Let P = HCNoRows();
For J = 1 to P
Let vTotal = vTotal + HCValue("Orçado",J);
Next
Add Load 'Total' as "Mensal", $(vTotal) as "Orçado" Autogenerate 1;

The following error occur:

"Load statement could not find target column [12629] in hypercube"

But the floor function does the same thing. The double quote haven't changed the problem. Not sure where to add this part though:

Add Load
'Total' as "Mensal",
sum("Orçado") as "Orçado",
Resident HC1;

[rwunderlich]

Replace the entire chart script with the suggested Add Load:

Add Load
'Total' as "Mensal",
sum("Orçado") as "Orçado",
Resident HC1;

-Rob