topic How can i achieve the chart expression in script editor itself? in QlikView

How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 06:46:30 GMT

Hi People,

can any one have the idea about achieve an chart expression in script editor itself?

below is the expression which i would like to get in the script itself.

(sum({<VersDesc = {'Adjust','Actual'},SalTypKey={'1'},SalPer = {1},Year=p(Year),Month={'*'},
Cal_Mon={'<=$(=max(Cal_Mon))'},CPTeaFlag={'CP','RPL','ORG'},DayNumInMon = {'<=$(=max(DayNumInMon))'},DayofWeek=,RegDesc={'*'},ACode=>}SalVal))/1000
)
/
count({<Month={'*'},Cal_Mon = {"<=$(=max(Cal_Mon))"},RegDesc={'*'},ACode=>}DISTINCT (FName))
/
count({<Month={'*'},Cal_Mon = {"<=$(=max(Cal_Mon))"},RegDesc={'*'},ACode=>}DISTINCT (Month))),'#,##0')

Can you guys help me how can i achieve this.... ??

Regards

Hemanth

Re: How can i achieve the chart expression in script editor itself?

Kushal_Chawda — Tue, 08 Aug 2017 06:48:53 GMT

Is there any specific reason to do it in script?

Re: How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 07:00:51 GMT

yeah there is reason the values are constant when i get into the chart and it wont change whenever a slection is done...

Re: How can i achieve the chart expression in script editor itself?

Kushal_Chawda — Tue, 08 Aug 2017 09:31:40 GMT

I did not understand "the values are constant when i get into the chart"

"and it wont change whenever a slection is done" -> Your data model is associative so, whatever selection you do, chart will reflect the values based on selection

Re: How can i achieve the chart expression in script editor itself?

gsbeaton — Tue, 08 Aug 2017 09:41:56 GMT

Not entirely sure what you are trying to achieve - is it just a case of maintaining the expression in the script? If so, then just create the expression as a variable, then call it from your expression dialogue box:

SET myExpression = sum({<country=>}Transactions);

Expression: $(myExpression)

Is this what you mean?

Re: How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 10:07:03 GMT

Exactly the data model is associative, whatever the selection i do the chart will reflection the values based on selection.

But my point of view is that the values should be constant whatever the selection i do. so that if i get this calculation in the script editor itself then the values are constant whatever the selection i made in the dashboard

Re: How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 10:12:44 GMT

can u give me a example....?? . so that i can understand in clear

Re: How can i achieve the chart expression in script editor itself?

techvarun — Tue, 08 Aug 2017 10:13:31 GMT

If you want to keep a static/Constant table then I suggest ValueList.

What is ValueList?

Re: How can i achieve the chart expression in script editor itself?

gsbeaton — Tue, 08 Aug 2017 10:20:25 GMT

Sure, in your script, just add this:



SET myExpression = sum({<country=>}Transactions);

Then in the expression window of your object:

You can then go one step further and create all of your expressions as variables and maintain them in an external CSV or Excel. Here's a link to some script I wrote to show you how to then process all that into variables: A QlikView and QlikSense script to generate variables from a CSV include. · GitHub‌

best regards

George

Re: How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 10:53:40 GMT

its not working george.. actually my aim of doing this for my role base security.

for example the above expression will give a value as 500 for whole india (Banglore, delhi, chennai, mumbai, hyderabad).

when i give role base security it splits according to the role base security like banglore =100, delhi =100, chennai =100, mumbai= 100 , hyderabad=100.

But what i would like to show is whatever my role based security still my expression has to show 500 but not 100

Re: How can i achieve the chart expression in script editor itself?

gsbeaton — Tue, 08 Aug 2017 11:07:27 GMT

Ah, well then this is something very different.

So, what you want to do is prepare some totals in the script. Your

expression is not going to work here.

You will need to create a new column of totals that each user has access to

and then use that in your expressions for showing totals.

On Tue, Aug 8, 2017 at 11:54 AM, Hemanth Aanichetty <

Re: How can i achieve the chart expression in script editor itself?

hemanthaanichet — Tue, 08 Aug 2017 11:12:20 GMT

as exactly u said i need the same but some suggestions or ideas how i can start this .... in a very big dilemma...

Re: How can i achieve the chart expression in script editor itself?

gsbeaton — Tue, 08 Aug 2017 14:08:03 GMT

You're going to have to figure out how you can update your data model to incorporate this change.

There are a lot of possible solutions, and without knowing the specifics of your problem, it's impossible to specify an exact solution, but I would imagine that you will have to do some kind of aggregation on your original data set before joining back in.

For example:

// Create a mapping table containing your aggregated values

MappingTable:

Mapping Load

Country

,Sum(Sales)

from datasource.QVD

Group By Country;

//Map in the totals for each country

MainTable:

Load

Country

,Sales

,SecurityKey

,ApplyMap('MappingTable', Country) AS TotalForCountry

from datasource.QVD;

In your expressions, you will have to be cognisant of the fact that you cannot aggregate your TotalForCountry column, so you will have to consider functions such as Only() or FirstSortedValue if your expression returns multiple values.

Hope this gets you started.

George