recalculates without selection - why and how to fix it?

[iliyansomlev]

Dear community,

Could you please take a look at this issue. It is about a table (a pivot in my case for the purpose of clearer presentation) that shows detailed row information from a data table with about 35 million rows. Other objects that summarize info from the same table work fine but the detailed (row-by-row) report takes a lot of time to calculate and furthermore recalculates every time some redesign is made (moving, sizing objects, etc.). It is very slow even though the process uses not more than 3 out of 8 GB RAM and CPU usage is about 50% .

Do you know if we can show the detailed report in a way that will improve calculation time and will not do recalculation on every redesign?

Thank you.

[Oleg_Troyansky]

Hi Iliyan,

a detailed Pivot Table that shows 35 mln rows will ALWAYS be very slow and heavy, and it will get recalculated with every little change - that's just how QlikView works.

The question is - do you REALLY need a Pivot Table with 35 mln rows? Is anyone going to read 35 mln lines? I don't even think that you can export 35 mln rows into Excel... So you have a few options here:

1. Use a Calculating Condition that requires from the user to narrow down the data set before the table gets calculated. For example, only calculate the data when a single Item or a single Customer is selected, or when count of transactions is less than 10,000...

2. Limit the table to a predefined number of top values (top Items, top Customers, etc...). See my recent blog article for a detailed description on how to do it. You can even go further and define dynamically, using a variable, how many Top values do you want to show. So, in the process of development, you may be showing only 100 values, and in production you open it up to 10,000 (whatever your server can bear...)

hope it helps,

Oleg Troyansky

www.masterssummit.com

www.naturalsynergies.com

[iliyansomlev]

Hi Oleg,

thank you for answering me. I am sorry that the uploaded app is so big, it takes 15 to 20 min to download. I wanted to give everyone direct experience of what I explain. The pivot table never shows more than 100 or so lines, usually they are about 50 lines, because it has the necessary Calculating Condition and also an additional condition (a "Calculate" buton should be pressed by the user to show the table info).

I cannot limit the table to a predefined number of top values because all values are equally important. Yet I do not agree that after the 50 lines are shown and I move or resize something without any selection it should take another minute to recalculate what is already shown and calculated. There is no logic for this to happen yet it does. It happens when the table is straight as well as pivot, or when I put concat (DVALUE) as expression instead of leaving everything as dimesnions with expression of 1 as it is in the attached app.

The only solution that I can see now is to replace the pivot with a table box. The latter works fine and fast but I lose the ability to hide repeating lines which I have with a pivot table.

I still hope to find another solution.

Cheers,

Iliyan