Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Pivot chart problem when collapsing row disappears

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?

1 Solution

Accepted Solutions
Highlighted
Master
Master

Re: Pivot chart problem when collapsing row disappears

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.

View solution in original post

4 Replies
Highlighted
Master
Master

Re: Pivot chart problem when collapsing row disappears

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.

Highlighted
Not applicable

Re: Pivot chart problem when collapsing row disappears

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?

Highlighted
Master
Master

Re: Pivot chart problem when collapsing row disappears

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.

View solution in original post

Highlighted
Not applicable

Re: Pivot chart problem when collapsing row disappears

Thanks! This works perfect!