Pivot chart problem when collapsing row disappears

[#1]

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?

[Anonymous]

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.

[#3]

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?

[Anonymous · Accepted solution]

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.

[#5]

Thanks! This works perfect!