topic Re: Pivot chart problem when collapsing row disappears in QlikView

Pivot chart problem when collapsing row disappears

Wed, 04 Jul 2012 06:34:43 GMT

In the attached example I made a very simple pivot, but when I collapse the first Column called 'Item' then line '1' disappears.

Any idea why this happens?

Re: Pivot chart problem when collapsing row disappears

Anonymous — Wed, 04 Jul 2012 06:39:52 GMT

In your expression you're comparing using the "Posting Date". For item 1 you have more than one posting date and since there's no aggregation (i.e. max([Posting Date]) or similar), QlikView will return Null() when you're comparing using Posting Date. The whole expression will result in null and be supressed.

Re: Pivot chart problem when collapsing row disappears

Wed, 04 Jul 2012 06:42:09 GMT

I more or less understand what you mean, but I don't know how to incorporate this in my expression so that this line will remain visible and the sum of the amounts is correct.

Any idea?

Re: Pivot chart problem when collapsing row disappears

Anonymous — Wed, 04 Jul 2012 06:47:45 GMT

Here's an expression that should work:

Sum(aggr(IF([Posting Date] < MonthEnd(Max(%KeyDate)) AND [Clearing Date] > MonthEnd(Max(%KeyDate)),sum(Amount)),Item,[Posting Date], [Clearing Date]))

It calculates the sum for every dimension combination and then sums them up accordingly so you should get the right total on each dim level regardless of how you expand your dimensions.

Re: Pivot chart problem when collapsing row disappears

Wed, 04 Jul 2012 06:50:31 GMT

Thanks! This works perfect!