Pivot Table

[#1]

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.

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

Thanks,

Liam

[marcus_sommer]

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

[#3]

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

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

[sasikanth]

hi,

Please Post your qvw

[#5]

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

[Mark_Little · Accepted solution]

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]

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.

[#8]

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

[marcus_sommer]

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