No change in structure of a pivot table

[#1]

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

[swuehl]

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

[#3]

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

[#4]

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

But i dont know if whats you are looking for

[#5]

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.

[#6]

is this kind of what you're looking for?

[#7]

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 · Accepted solution]

Maybe like attached?

[#9]

thats perfect. Thanks Stefan