Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Precalculate expressions in script

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

jjbom1990

Creator

2016-09-21
07:53 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Precalculate expressions in script

Hi guys,

I have a quick question and I cannot find a definitive answer.

is it possible to calculate expressions that are used in charts in the load script? as within a preceding load?

I have this expression:

=sum({<FactType= {'Vlottende Activa'}>}Waarde)/SUM({<FactType = {'Kortlopende Schulden'}>} Waarde)

can I in some way precalculate this and put this into a field? and if this is possible is it even useful to do this? Could this potentially speed up my app? or is it going to be slower if I do this for every graph. The problem right now is that my app needs 1.5 seconds to change when I change a filter. it has about 3.5 mln facts with each set it own facttype, the fact table it self only has 16 fields. It shouldn't be this slow, it is even worse when it is on a server.

Thanks in advance!

Jasper

1,152 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2016-09-22
08:46 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It all depends on what your dimensions are and you can pre-calculate the Sums or even the division. Here is a small sample for you where I am assuming that you want to aggregate this on the Dim dimension

**Table:**

**LOAD * Inline [**

**Dim, FactType, Waarde**

**A, Vlottende Activa, 10**

**A, Kortlopende Schulden, 15**

**A, Vlottende Activa, 20**

**A, Kortlopende Schulden, 10**

**A, Vlottende Activa, 23**

**A, Kortlopende Schulden, 30**

**B, Vlottende Activa, 30**

**B, Vlottende Activa, 20**

**B, Kortlopende Schulden, 60**

**B, Kortlopende Schulden, 40**

**C, Vlottende Activa, 30**

**C, Kortlopende Schulden, 40**

**];**

**Left Join (Table)**

**LOAD Dim,**

** Sum(If(FactType = 'Vlottende Activa', Waarde)) as Sum_Vlottende_Activa,**

** Sum(If(FactType = 'Kortlopende Schulden', Waarde)) as Sum_Kortlopende_Schulden,**

** Sum(If(FactType = 'Vlottende Activa', Waarde))/Sum(If(FactType = 'Kortlopende Schulden', Waarde)) as Percentage**

**Resident Table**

**Group By Dim;**

Now for comparison, I will show you how this will look in three different scenarios

Dimension

Dim

Expressions

1) **Percentage**

2) **Sum_Vlottende_Activa/Sum_Kortlopende_Schulden**

3) **=Sum({<FactType= {'Vlottende Activa'}>}Waarde)/Sum({<FactType = {'Kortlopende Schulden'}>} Waarde)**

But since now we have done this for Dim dimension, we probably won't be able to use this for any other dimension. Lets say we want to use them in a dimension less text box and you will see that you won't be able to replicate the result of the third expression with the other two expressions. Unless you create another set of aggregation, you won't be able to use it in text box object.

So, in short if you know what the dimensions are going to be, then go ahead with this. Else, this could become very challenging very quickly specially the percentage. Sums show still be good.

HTH

Best,

Sunny

5 Replies

sunny_talwar

MVP

2016-09-21
12:22 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It is possible to do this, but there might be multiple steps involved using Resident Load with Group By statements and where or if clauses.

miguelbraga

Partner - Specialist III

2016-09-21
12:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hey there,

You can make this like Sunny T have told you, but you do this using flags in preceding loads like this:

Table:

LOAD *,

(sum(Waarde) * Flag1) / (sum(Waarde) * Flag2) as YourSetExpression;

LOAD *,

if(FactType = 'Vlottende Activa', 1, 0) as Flag1,

if(FactType = 'Kortlopende Schulden', 1, 0) as Flag2;

LOAD *

FROM [your connection to your database goes here];

Something so simple as this can solve you many problems in performance. Try to bring to script as maximum complexity as you can, so the rendering in charts decrease in time and you'll get the best performance in your dashboard.

Best regards,

Data Architect MB

jjbom1990

Creator

2016-09-22
03:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Miguel,

I tried your approach first, how ever when I try to reload the script it gives me an invalid expression error. Though it seems like everything is set up correctly.

LOAD

*,

(sum(Waarde)*[Vlottende activa])/(sum(Waarde)*[Kortlopende schulden]) as [Liquiditeit];

LOAD

*,

If(FactType = 'Vlottende Activa', 1,0) as [Vlottende activa],

If(FactType = 'Kortlopende Schulden', 1,0) as [Kortlopende schulden]

am I missing something here?

Thanks in advance.

Jasper

949 Views

jjbom1990

Creator

2016-09-22
03:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi sunny T,

Thank you for your reply, is there any discussion where an example is given by any chance? or do you have a quick syntax example?

Thanks in advance

Jasper

949 Views

sunny_talwar

MVP

2016-09-22
08:46 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It all depends on what your dimensions are and you can pre-calculate the Sums or even the division. Here is a small sample for you where I am assuming that you want to aggregate this on the Dim dimension

**Table:**

**LOAD * Inline [**

**Dim, FactType, Waarde**

**A, Vlottende Activa, 10**

**A, Kortlopende Schulden, 15**

**A, Vlottende Activa, 20**

**A, Kortlopende Schulden, 10**

**A, Vlottende Activa, 23**

**A, Kortlopende Schulden, 30**

**B, Vlottende Activa, 30**

**B, Vlottende Activa, 20**

**B, Kortlopende Schulden, 60**

**B, Kortlopende Schulden, 40**

**C, Vlottende Activa, 30**

**C, Kortlopende Schulden, 40**

**];**

**Left Join (Table)**

**LOAD Dim,**

** Sum(If(FactType = 'Vlottende Activa', Waarde)) as Sum_Vlottende_Activa,**

** Sum(If(FactType = 'Kortlopende Schulden', Waarde)) as Sum_Kortlopende_Schulden,**

** Sum(If(FactType = 'Vlottende Activa', Waarde))/Sum(If(FactType = 'Kortlopende Schulden', Waarde)) as Percentage**

**Resident Table**

**Group By Dim;**

Now for comparison, I will show you how this will look in three different scenarios

Dimension

Dim

Expressions

1) **Percentage**

2) **Sum_Vlottende_Activa/Sum_Kortlopende_Schulden**

3) **=Sum({<FactType= {'Vlottende Activa'}>}Waarde)/Sum({<FactType = {'Kortlopende Schulden'}>} Waarde)**

But since now we have done this for Dim dimension, we probably won't be able to use this for any other dimension. Lets say we want to use them in a dimension less text box and you will see that you won't be able to replicate the result of the third expression with the other two expressions. Unless you create another set of aggregation, you won't be able to use it in text box object.

So, in short if you know what the dimensions are going to be, then go ahead with this. Else, this could become very challenging very quickly specially the percentage. Sums show still be good.

HTH

Best,

Sunny