Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

No change in structure of a pivot table

Hi,

I am currently using Qlikview 10. Now, I have got a P&L statement with various dimension values like Revenue, Cost of Sales etc that are related to various account types.

Now my problem is when I filter on any of these values, the rows in my P&L statement are also filtered. However, I want to make the statement structure independent of the filters. This means that if I select any value (say Cost of Sales), I need the structure to stay as it is (and not filter on Cost of Sales and delete other rows). All the other rows can be blank but the staructure should not change. I know it goes against the logic with which Qlikview works but thats a client requirement.

Please help.

Cheers

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached?

View solution in original post

8 Replies
swuehl
MVP
MVP

You could try clearing the selection in AccountType for any expression in your pivot table.

For example, in your first expression you could use:

=IF(Type = 'Blank' or Type = 'Header', ' ',sum({<AccountType=>} InMonth(PLDate,vMaxDate,0,0) * -1 * Amount * Sign))

and for your text format expressions:

=only({<AccountType=>} Format)

If you apply similar set expressions to all your expressions and formats, this should make your pivot table insensitive to selections in AccountType. If you only want to preserve the structure, but set the non selected lines to zero. you could maybe create an additional expression like

sum({1} Amount)

and then hide it in presentation tab.

Hope this helps,

Stefan

Not applicable
Author

Thanks Stefan. I tried making the changes but they dont seem to be working. Could you please make the changes you are talking about in the qvw I attached earlier and attach it back into this thread.

Cheers

Not applicable
Author

This should be inmune to any filter (except for explicit time calculations).

But i dont know if whats you are looking for

Not applicable
Author

Thanks for that Federico. Part of what youve done is right. I need the structure to stay the same. However, when I select on a particular account type, I want the profit and loss statement to be calculated only for the selected Account Type, the rest should be 0. If no Account Type is selected, then it should show me the values for all rows in the PLN statement.

Not applicable
Author

is this kind of what you're looking for?

Not applicable
Author

Thanks Juan. Getting closer. I neeed to do what you have done for all the columns and not just one column. Also, I need the calculated value only for that row and that associated total row. For example, if I select Reveneue,I only need to calculate for Revenue. At the moment it happens to calculate the numbers for all totals (like Gross profit, Operating Profit etc)

swuehl
MVP
MVP

Maybe like attached?

Not applicable
Author

thats perfect. Thanks Stefan