Qlik Community

QlikView Administration

Discussion Board for collaboration on QlikView Management.

cancel
Showing results for 
Search instead for 
Did you mean: 
ryanb1982
Contributor II
Contributor II

Ignoring Selection in Pivot Table

Hello, everyone! I have a simple pivot table that includes parent account and account as dimensions and then displays a number of numerical expressions. If there are no selections/filters, the pivot table shows all parent accounts and then all individual accounts nested underneath each parent, followed by the values. However, if a user filters on a particular account, instead of limiting the pivot table to a single row with that account, I'd like the pivot table to limit on the parent account dimension instead so that the user can see each of the related accounts rolling up to the same parent as the one selected.  Is this possible?  I cannot use parent account as the filter because there are other objects on the sheet that would be adversely affected.  Thanks in advance for your help!


Here's an example:

example.jpg

If the user filters on Account 1, I want the pivot to show all the values for its Parent Account (1-4) and not just limit the pivot to the row for Account 1.

1 Solution

Accepted Solutions
hector_munoz
Specialist
Specialist

Hi Ryan,

With a variable like:

vdParentAccountsAvailables ='|' & Concat([Parent Account], '|') & '|'

, and an expression like:

Sum({1} If(SubStringCount(vdParentAccountsAvailables, [Parent Account]) > 0, Sales))

, you should get what you need... I attach you a sample. Hope it helps you!

Regards,

H

View solution in original post

3 Replies
hector_munoz
Specialist
Specialist

Hi Ryan,

With a variable like:

vdParentAccountsAvailables ='|' & Concat([Parent Account], '|') & '|'

, and an expression like:

Sum({1} If(SubStringCount(vdParentAccountsAvailables, [Parent Account]) > 0, Sales))

, you should get what you need... I attach you a sample. Hope it helps you!

Regards,

H

View solution in original post

ryanb1982
Contributor II
Contributor II
Author

Hector,

Thank you so much for this very clever solution!  I modified the expression as follows:

=if(count({1} If(SubStringCount(vdParentAccountsAvailables, [Parent Account) > 0, Account)) > 0,count({1<Stage={'*'}-{'Rejected*'}>}[Proposal]),0)

This way I could still use set analysis to form the expression for any of the dimensions that met the "count" condition you provided using the variable.

Cheers!

Ryan

hector_munoz
Specialist
Specialist

Very well solved, Ryan! Congrats!