Why is a Pivot Table with conditional show disappearing, even though ShowMode is Conditional: Normal

[#1]

I have a pivot table with some heavy calculation (and a large application with 250M+ rows) and that uses Conditional Show/Hide that sometimes disappears and stays 'disappeared'. I check the sheet properties and sure enough, the pivot table exists (was not deleted) and is in ShowMode Conditional: Normal, but there is no chart to be had.

I have checked and rechecked the Caption x/y position and height/width for Normal and Minimized (in the Caption settings of the object) and it is set to be in the middle of the screen and big for both normal and minimized.

Sometimes I can get the chart to reappear if I remove a dimension or do something (I can't detect a pattern). Other times, no luck. Even changing the ShowMode to "Always" doesn't bring it back.

I have noticed that this chart takes a lot of time to calculate. When I have managed to get the chart to appear, then it doesn't want to calcuate. (X-out w/ the refresh icon in the corner, which doesn't get it to recalculate).

Am I missing something?

Thanks!

<<QlikView Developer 8.5; 64-bit 128 GB server w/ 4 quad core processors.>>

[Oleg_Troyansky]

Tyler,

you listed all the reasons for such an unpredictable behavior:

Tyler Waterfall wrote:I have a pivot table with some heavy calculation (and a large application with 250M+ rows)

While QlikView is very well scalable, if you have 250M+ of rows, you want to try and avoid "heavy calculations". You should be able to simplify your expressions and make the chart "lighter":

1. DEFINITELY avoid any IF calculations inside aggregated functions (like sum(IF(...) - those are killing performance, no matter what other opinions you might read on this forum...

2. If you use Set Analysis, avoid listing hundreds or thousands of values via $-sign expansions, those are slow, too.

3. Think about any opportunity to move any heavy calculations from the run-time into load-time, without sacrificing granularity of your data.

4. If your Pivot Table is supposed to produce thousands and thousands of rows, it will be slow no matter what - limit its calculation by a calculation condition (request that the user makes relevant selections before you show them the details).

Once you make your Pivot Table less heavy, I'm pretty sure it will "behave" again...

good luck!

[#3]

Oleg,

I understand and appreciate your thoughts about lightening the chart. I agree that those guidelines are good to follow. (We've experienced the pain of IF and SET expressions!)

However, I am not using any IF or SET or AGGR expressions. The most complex is a % of total: Sum (field * binary flags) / sum ( TOTAL field * binary flags). Although it is possible to generate thousands of rows, the chart initially generates 1 or 2 thousand rows. (Is this too big?) Still, maybe I mispoke by saying it was "heavy".

I'm still concerned that the Chart disappears, regardless of how much CPU or time it takes to calculate. It should time out or something, not disappear!

So, if I am constrained by business requests for the pivot table to have the existing expressions, is there any hope for me?

Thanks again for your input.

[richnorris]

Hey Tyler, did you ever find out a solution to this? I am having the same problem, except I am only showing a table, with no calculations at all, and it will permanently disappear when switching between show conditions. Frustrating!

[#5]

Rich,

I've not found any solution for this. Sometimes I can close a document and open it back up and the chart reappears. Other times, I edit the object properties (Sheet Properties > Sheet Objects > select 'hiding' chart > Properties). If I edit the right thing -- like change the Conditional Show to Always or disable an expression, the chart will come back. Then I reset whatever temporary change I made. It's a bit frightening that objects selectively disappear.

[richnorris]

Thanks Tyler, I've found pretty much the same thing, and am getting this behaviour even if I am working with an empty data set, so its clearly nothing to do with running out of memory to draw the object. As you say, it is especially concerning in its inconsistency, I've used exactly the same conditional formatting elsewhere in my document without any fuss? Oh well, if I do figure anything out I'll let you know, but it looks like its probably just going to come down to another reason to upgrade.