Performance expression issue

Thu, 24 Aug 2017 07:21:58 GMT

Hi All,

I try to create a chart displaying number of distinct customers with 3 conditions.

Past two years depending the start date chosen by users (there is also an end date but used for other charts and expressions) i would like to count:

- Distinct customers with only one order during this period

- First order made past year

- Never been VIP during this period

This is my expression :

Count(DISTINCT {<[Order Date]={">$(vLast2Years) <$(vStartDate)"}>}

If(

Aggr(count( DISTINCT {<[Order Date]={">$(vLast2Years) <$(vStartDate)"}>} [Order Id]), [Customer Id])=1

and

Aggr(min({<[Order Date]={">$(vLast2Years) <$(vStartDate)"}>} [Order Date]), [Customer Id]) >= vLastYear

and

Aggr(max ({<[Order Date]={">$(vLast2Years) <$(vStartDate)"}>} [Is VIP]), [Customer Id])=0

, [Customer Id]))

It's working but it's very slow, i need to wait more than 30 sec when i change the Start Date.

My model is very simple, it's just a table with four fields ( Customer Id, Order Date, Is VIP, Order ID) and 3 millions rows.

I think the main cause of this poor performance is my server configuration.

It's running on VM with 4 CPU and 12GB RAM, the processor is an Intel Xeon CPU E5-2640 0 2.50 GHz.

QV version : QlikView 11.2 SR10.

Any advice to impove my expression ?

Cheers !

Re: Performance expression issue

greg-anderson — Thu, 21 Sep 2017 15:37:14 GMT

I'm sorry I cannot help with the question itself, but I wouldn't be convinced that it's the server configuration.

I have a small QlikView application on my server (4x10 CPU at 2.8GHz, 512GB RAM).

Against my advice (and our best practices), this application contains multiple embedded IF expressions with aggregations, as well as calculated dimensions and formatting of similar complexity.

Not only is the solution slow, it only take 5 concurrent users before I see an impact on the QlikView Server performance as a whole. I can actually watch CPU usage spike when users make filter selections.

I am trying to isolate the problematic expressions now.

topic Performance expression issue in QlikView

Performance expression issue

Re: Performance expression issue