Problem in loading table in Sense app

[jasmina_karcic]

Hi experts,

I have problem. I installed new version of NPrinting and Qlik Sense (June 2018).

Now I have a problem in loading table in application. It takes too much time. Because of that, NPrinting cannot generate report.

It gives me message:

WARN: error during report generation: Object pkvJPge of Sense app 29c327dd-cf23-4f24-9559-95950477bc23 failed to render. ERROR: Method "GetLayout" timed out

I don't understand anything, what is this.

😕

Thanks

[Lech_Miszkiewicz · Accepted solution]

I am sorry, you dont need advice. You need experienced consultant to help you improve performance of your app on site. 

In my opinion expressions you are using are not optimal, data model can be done better and since you are using this app only for nprinting you dont need to use rangemin functions etc.... it all should be done in script, using flags

[jasmina_karcic]

How do you mean aggregation in qlik script, not in chart?

😕

[jasmina_karcic]

Thank you

[Lech_Miszkiewicz]

I wish i could help you more. 

My advice is:

• Review what dimension are you using in your tables in Qlik Sense front end tables
  • If for example you are having dimensions like country, product, date, salesperson and you have sales_amount column on data load in qlik sense script apply aggregation like this:

Load

Country, 

Product,

Date,

Salesperson

Sum(sales_amoun) as sales_amount

From

YourQvd.qvd (qvd)

Group by

Country, 

Product,

Date,

Salesperson

;

Ideally you want this data to be aggregated in data source/ data warehouse to the required level to avoid this aggregation in load script, but if this is not possible then it will still be better than not aggregating data at all and rooling everything up from lowest grain on Qlik Sense frontend. 

In general make sure you aggregate data in your data model!!

[jasmina_karcic]

Lech, how can I write in data model this expression:

fabs(Sum({$<DATUM={"$(=date(rangemin(MONTHEND(today()-1,-13),MONTHEND(MAX(DATUM),-12))))"},  IZVESTAJ={"MIS_BSS"}>}STANJE_KM)/(1000)) as BS_12

with if?

Jasmina

[jasmina_karcic]

I wrote:

if(IZVESTAJ='MIS_BSS' AND DATE(DATUM)=DATE(MONTHEND(today()-1,-13)), SUM(STANJE_KM) ) AS BS_12

It takes much time for load....

😕

I need maybe 100 formulas like this...

Jasmina

[jasmina_karcic]

Lech, I think that is not good idea...this cannot be

[Lech_Miszkiewicz]

Well - I am not sure if you understood what i meant by saying that you need to move your expression back to script. We did not say that you have to replicate your UI expressions, but all what we said is to roll it up to the level which will still allow you to use chart expressions on qlik sense front end, but your expression will be run against a lot smaller dataset.

What i meant by aggregating data in data model was to rollup to max possible aggregation in script in order to reduce your fact table. Expressions on front-end are still necessary but they can be optimized!

Out of 400 millions i am sure there is a way to preaggregate data in datamodel to for example 50 milion of rows... or maybe less

Now when i look at your expression i see that:

• you are summing up Stanje_KM and returning absolute value
• you are filtering date by checking min date out of MONTHEND(today()-1,-13), and MONTHEND(MAX(DATUM),-12)))
  • this check possibly can be done in script with if statement and could return min date for the row or flag which you can then directly use in set analysis
• You can divide STANJE_KM by 1000 in script
• you can also use Sum(STANJE_KM) in script and group it by all required fields (all dimensions used in your NPrinitng report)

It is very hard to advice you on this. I would have to know how the NPrinting report needs to look like

• all dimensions,
• all filters used
• all logics in template etc..

On Qlik Sense application side we would need to see:

• load script
• data model preview
• all expressions from objects used for NPrinting report
• all objects used in NPrinting report so we could see dimensions, as the data on backend should be aggregated up to them (grouped by ALL dimensions used in UI)
  
• data which is getting loaded into this app
  • what is the date range?
  • what is the date grain (daily/date, weekly/weekend, monthly/monthend)?
    • Is the data pre-aggregated in data model to the max required for nprinting level -  i say currently it is not!?
    • How much history are you loading? are you only loading data required for NPrinting report?

Like i said - i cannot guide you here as i would need to understand whole application to give you right guidence. This really can be done by having the app in front of you and going through it from requiremnts, source, script till UI and NPrinting.

regards

Lech

[Lech_Miszkiewicz]

No - this is not the way you need to do this. We cannot look at this expression by expression. We need to have full picture to be able to advice accordingly.

I said it before in one of my answers before, that typicaly this aggregation should be done in data warehouse....

In my last previous response i also explained that we don't want to replicate chart expressions in script, we just want to preaggregate data...

I really think you need someone who is experienced in Qlik development to help you with this. We will not be able to this via community since we don't have a full picture.

By the way - what is the source of data? Is is database or data warehouse? I would push a lot of transformation down to source if possible.

regards

Lech

[jasmina_karcic]

Look this Lech,

I have turned back snapshot of server, and with it I turned back previous version of Qlik Sense and NPrinting (april 2018) and now all works fine.

But I am afraid, again will stop, it is just question of moment.

P.S. Database is source (we created many views in relational database)

regards,

Jasmina