topic Re: Chart expression Vs Memory utilization in QlikView

Chart expression Vs Memory utilization

amit_saini — Fri, 23 Oct 2015 10:42:04 GMT

Hi Folks,

We are facing serious issue with server memory utilization, sometimes it is exceeding more than 100% and this leads to server machine crash.

Although we have restricted memory utilization from QMC max upto 80% of the total machine memory. Finally we found reason for this from the Audit log file of QlikView server that one of our application chart is causing this issue . When user trying to access this chart, it's not responding for 15-20 mints and during this time frame we can see from the task manager that memory increasing from 20,30% upto 100% and crashing server.

We are using below expression in chart :

Sum ({<Complaint_Acceptance-={4}>}QTY_PPM*Top_Pos_Flag)/
if(Complaint_Type=3 or Complaint_Type=1, aggr(nodistinct Sum (QTY_Delivered_TOTAL), Month),
if(Complaint_Type=2 or Complaint_Type=7, aggr(nodistinct Sum (QTY_Received_TOTAL),Month),0))
*1000000

Please suggest what shall we do for this expression so that chart will respond in short period of time like other.

I tried this expression to get rid off aggr function , but output is not matching.

Sum ({<Complaint_Acceptance-={4}>}QTY_PPM*Top_Pos_Flag)/
sum(
if(match(Complaint_Type, 1, 3), QTY_Delivered_TOTAL,
if(match(Complaint_Type, 2, 7), QTY_Received_TOTAL,
0))
)
*1000000

Thanks,

Re: Chart expression Vs Memory utilization

Anonymous — Fri, 23 Oct 2015 10:51:03 GMT

Without trying this with your data, I would assume the if-statement is your problem.

What if you, in your script, create a new field, called QTY_Total, and use that in your chart:

if(Complaint_Type=3 or Complaint_Type=1, Sum (QTY_Delivered_TOTAL),if(Complaint_Type=2 or Complaint_Type=7, Sum (QTY_Received_TOTAL),0)) AS QTY_Total

Then you should be able to remove the if's, writing your expression like:

Sum ({<Complaint_Acceptance-={4}>}QTY_PPM*Top_Pos_Flag)/aggr(nodistinct Sum (QTY_TOTAL), Month))*1000000

Does that make a difference?

Re: Chart expression Vs Memory utilization

Gysbert_Wassenaar — Fri, 23 Oct 2015 11:00:09 GMT

Maybe like this:

Sum ({<Complaint_Acceptance-={4}>}QTY_PPM*Top_Pos_Flag)/

alt(pick(Complaint_Type,

sum(total <Month> QTY_Delivered_TOTAL),

sum(total <Month> QTY_Received_TOTAL),

sum(total <Month> QTY_Delivered_TOTAL),

sum(total <Month> QTY_Received_TOTAL)

),0)

*1000000

Re: Chart expression Vs Memory utilization

swuehl — Fri, 23 Oct 2015 11:27:02 GMT

Are you sure you want to divide by zero for Complaint_Type other than listed?

It's hard to help you without knowing a little more about the context of your expression (in which chart you are using it, with what dimensions etc.) and your data model.

In general,

- use aggregation functions

Use Aggregation Functions!‌

It’s all Aggregations

- take care that your expression does not need to create JOINS between large tables, especially JOINS with no key values in common

The Calculation Engine

Re: Chart expression Vs Memory utilization

amit_saini — Fri, 23 Oct 2015 11:40:09 GMT

Yes Swuehl,

We are doing this based business requirement.

Unfortunately this application datamodel is very complex. Let me follow your suggestions , hope this will help me to solve the problem.

Thanks,

Re: Chart expression Vs Memory utilization

amit_saini — Fri, 23 Oct 2015 11:54:39 GMT

Hi Gysbert,

Please see below :

Now only issue with this graph , this expression below is causing issue :

Sum ({< Complaint_Acceptance-={4},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_PPM*Top_Pos_Flag) /
Sum ({<Complaint_Type={3,1},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_Delivered_TOTAL)*1000000

where

vRollingDate=num(MonthStart(AddMonths(max(NumDate),-11)))

vRollingDateEnd==floor(num(MonthEnd(max(NumDate) ) ))

Thanks,

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 05:10:25 GMT

Hi Folks,

Please help me on below expression , it's consuming more than 100% of machine memory.

where

vRollingDate=num(MonthStart(AddMonths(max(NumDate),-11)))

vRollingDateEnd==floor(num(MonthEnd(max(NumDate) ) ))

Is there any better way to write this expression????

Thanks

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 05:12:03 GMT

jagan‌ tresesco‌

Any suggestions????

Thanks,
AS

Re: Chart expression Vs Memory utilization

Gysbert_Wassenaar — Mon, 26 Oct 2015 05:44:42 GMT

I don't see a way to optimize that expression other then leaving out anything that isn't strictly needed. I suspect your problem is the data model of your document. And possibly the amount of data in your document compared to the amount of ram your document has.

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 05:50:42 GMT

Yes you are right I'm having very complex datamodel.

Looks to me I have to work on this.

Thanks,

Re: Chart expression Vs Memory utilization

jagan — Mon, 26 Oct 2015 05:58:56 GMT

Hi Amit,

Is this two columns QTY_PPM and Top_Pos_Flag are in same table or in different table? If possible calculate (multiply) this in the script itself and check the performance.

Regards,

Jagan.

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 06:05:19 GMT

Also I just want to tell you , if I'm using either of the expression alone without division than charts are acting within seconds.

Only issue while doing "/" , which is strange to me.

Thanks,
AS

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 06:41:55 GMT

Hi Jagan,

Thanks for ur suggestion!

Both are in same table , i tried this approach too . Still same issue

Thanks,

Re: Chart expression Vs Memory utilization

jagan — Mon, 26 Oct 2015 06:54:23 GMT

Hi Amit,

When you are calculating both the expressions separately are you getting this issue?

Expression1: Sum ({< Complaint_Acceptance-={4},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_PPM*Top_Pos_Flag)
Expression2: Sum ({<Complaint_Type={3,1},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_Delivered_TOTAL)

OR try this

=(Sum ({< Complaint_Acceptance-={4},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_PPM*Top_Pos_Flag) /
Sum ({<Complaint_Type={3,1},Month=,Year=, Day=, Year_Month=, NumDate = {">=$(vRollingDate)<=$(vRollingDateEnd)"} >} QTY_Delivered_TOTAL))*1000000

Regards,

Jagan.

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 07:01:55 GMT

Hi Jagan,

When I'm using them separately , no issue at all . The only problem when I'm using them together.

I tried your second suggestion too , still same issue.

But what I found is , if I'm converting this into Straight table instead of line chart then it is working fine.

Thanks,

Re: Chart expression Vs Memory utilization

jagan — Mon, 26 Oct 2015 07:04:28 GMT

Hi Amit,

Try creating the new object and check, sometimes it works.

Regards,

Jagan.

Re: Chart expression Vs Memory utilization

amit_saini — Mon, 26 Oct 2015 07:28:56 GMT

Hi Jagan,

It's really strange for me , but it is really working after creating new object. Is this bug in QlikView or something else???

Thanks a lot!

Re: Chart expression Vs Memory utilization

jagan — Mon, 26 Oct 2015 07:32:55 GMT

Really strange, but sometimes it is happening like that, faced this lot of times.

Regards,

jagan.