topic Re: Limit a Pivot due to Performance Problems in QlikView

Limit a Pivot due to Performance Problems

chriys1337 — Fri, 08 Dec 2017 15:48:38 GMT

Hi all,

I am building a "Self-Service" Pivot Table,

where the user can select 1 to 35 dimensions to be shown and there is (just) one expression.

Depended on how many dimensions the user has selected, the more bigger the pivot gets.

This pivot has a very bad performance on the Access Point and I wanted to ask for help, something like a condition (under Layout, Condition) to check how many datasets / rows are displayed. The question is, how should the condition look like:

I have tried to use getpossiblecount(Dim A) * getpossiblecount(Dim B) * etc.

and also with count(aggr(1,[Dim A],[Dim B))

I am not really coming further. Did anybody already touch this performance topic of a self-service pivot, who could please give a advice?

Kind Regards and thank you!

Re: Limit a Pivot due to Performance Problems

sunny_talwar — Fri, 08 Dec 2017 15:50:56 GMT

It might be easy to help if you are able to share a sample?

Re: Limit a Pivot due to Performance Problems

chriys1337 — Fri, 08 Dec 2017 16:03:41 GMT

I have uploaded one small application, which is showing the starting position.

Of course this example does not have any performance issue.

But my question is how can I find a limitation / expression, which is counting the data rows.

And if the data rows are exceeding a specific amount, than the table should not be displayed anymore.

Re: Limit a Pivot due to Performance Problems

sunny_talwar — Fri, 08 Dec 2017 16:09:52 GMT

May be try this expression

=Sum(Aggr(If(Sum(Performance) <> 0, 1, 0), $(=Concat('[' & Field_Display_Name & ']', ', '))))

The above will give you the number of rows that are getting displayed in the chart

Re: Limit a Pivot due to Performance Problems

chriys1337 — Fri, 08 Dec 2017 16:14:50 GMT

So basically, you used

sum(aggr(1,DimA,DimB).

what is aggr(1,

doing?

Can you explain a little bit please?

Re: Limit a Pivot due to Performance Problems

sunny_talwar — Fri, 08 Dec 2017 16:16:50 GMT

Aggr(1? Where do you see that in my expression? I am confused

Re: Limit a Pivot due to Performance Problems

chriys1337 — Fri, 08 Dec 2017 16:18:57 GMT

(If(Sum(Performance) <> 0, 1, 0),

if the performance is not 0, than 1

so 1

so -> sum(aggr(1,DimA....)

Re: Limit a Pivot due to Performance Problems

sunny_talwar — Fri, 08 Dec 2017 16:23:06 GMT

I guess, you can put it that way... but I wouldn't use straight 1 because if you have set analysis in your expression... using just 1 won't be enough... I would use the expression itself and check if it is <> 0.

Anyways, what is the part you don't understand about the expression? I am still not sure I understand what is wrong with this?

=Sum(Aggr(If(Sum(Performance) <> 0, 1, 0), $(=Concat('[' & Field_Display_Name & ']', ', '))))

Re: Limit a Pivot due to Performance Problems

chriys1337 — Tue, 12 Dec 2017 15:11:33 GMT

Hi Sunny,

thanks,

my question is:

1. What is sum(aggr(expression,dim1,dim2,dim3) doing? why not sum(aggr(dim1,dim2).

2. I also don't get why are you checking if the performance is not 0? (expression)

Thx a lot.

Chris

Re: Limit a Pivot due to Performance Problems

sunny_talwar — Tue, 12 Dec 2017 15:48:39 GMT

What does this even mean?

sum(aggr(dim1,dim2)

If I know what it means, I might be able to let you know... but I have no idea what it does....

For your second question...., I only want to sum when your expression has a value... that is why checking for 0.

Is it not giving you what you want? I am not sure what the issue is...