Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Pivot Table

Hi,

I'm having a issue with a pivot table. I'm using a dimension called BSLevel to assign different numbers (1, 2, 3) in order to organise the balance sheet I'm creating in terms of Totals (1's) Normal Row's (2's) and Headings (3's). I have assigned the BSLevel numbers to the correct row's as seen in image 1. The problem is that when I make any current selection the BSLevel 3 numbers disappear as seen by image 2. Because of this I can't use the BSLevel 3 to bold the headings in my balance sheet as when they disappear e.g. when year is selected, text format in the pivot table does not pick my expression "=if(BSLevel = '3', '<B>')".

  • The BSLevel 3 row's do not contain any information
  • In the dimension tab, suppress when value is null is unticked and show all values is ticked
  • In the presentation tab, suppress zero-values is unticked and suppress missing is ticked


Any help with why BSLevel 3 is not showing up when there is a current selection would be greatly appreciated.


Image 1: Balance Sheet with no selection with the BS level 3's showing up and the corresponding headings being bold as intended.

BS no selection.PNG


Image 2: When there is a current selection, no BSLevel 3's show up and the corresponding headings are not in bold.

BS selection.PNG


Thanks,

Liam

1 Solution

Accepted Solutions
Partner
Partner

Re: Pivot Table

Hi,

First of all I would add the bold expression to the Text formatting under  the dimension tab, if it is not already.

Then regards to the disappearing values, we need to make sure we are returning a single value, I would advise something like the below.

If(only({1}BSLevel) = 3, 3,

     'Your expression'

)


- Mark

8 Replies
MVP & Luminary
MVP & Luminary

Re: Pivot Table

I'm not sure if I understand you right but to get independent from any selection you need set analysis expressions: A Primer on Set Analysis.

- Marcus

Not applicable

Re: Pivot Table

I've used set analysis within the expression, is this what you mean?

sum({$<FY={$(vYTDYear)},PeriodStatus={'Complete'},AmountFlag={'Actual'}>} Amount)

sasikanth
Valued Contributor III

Re: Pivot Table

hi,

Please Post your qvw

Not applicable

Re: Pivot Table

Unfortunately the data I'm working with is confidential so i'm unable to upload the qvw.

Partner
Partner

Re: Pivot Table

Hi,

First of all I would add the bold expression to the Text formatting under  the dimension tab, if it is not already.

Then regards to the disappearing values, we need to make sure we are returning a single value, I would advise something like the below.

If(only({1}BSLevel) = 3, 3,

     'Your expression'

)


- Mark

gsbeaton
Contributor II

Re: Pivot Table

Hi Liam,

There are a couple of possibilities

  1. You don't have any transactional data that is linked to your balance sheet items or calendar
  2. There is an issue with how you are aggregating your transaction items.


Those may give you a clue to further investigation.  If you're still having problems, post a screenshot of your table viewer, specifically showing linkage between your calendar, transactions and your balance sheet items and details of your expressions we can have a closer look.


Not applicable

Re: Pivot Table

Thanks very much Mark. That got it to pick up the level 3's. Really appreciate it!

MVP & Luminary
MVP & Luminary

Re: Pivot Table

Yes, with this kind of expressions - maybe extended to a 1 as set qualifier and a TOTAL - you could display data independent from certain or any selections. In addition to your bold-formatting wouldn't it be easier to use the level as superior dimension with enabled sub-totals?

- Marcus