topic Re: Pivot Table in QlikView

Pivot Table

Mon, 02 Nov 2015 10:25:36 GMT

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

Re: Pivot Table

marcus_sommer — Mon, 02 Nov 2015 10:34:56 GMT

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

Re: Pivot Table

Mon, 02 Nov 2015 10:45:29 GMT

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

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

Re: Pivot Table

sasikanth — Mon, 02 Nov 2015 11:02:11 GMT

hi,

Please Post your qvw

Re: Pivot Table

Mon, 02 Nov 2015 11:05:36 GMT

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

Re: Pivot Table

Mark_Little — Mon, 02 Nov 2015 11:17:10 GMT

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

Re: Pivot Table

gsbeaton — Mon, 02 Nov 2015 11:22:34 GMT

Hi Liam,

There are a couple of possibilities

You don't have any transactional data that is linked to your balance sheet items or calendar
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.

Re: Pivot Table

Mon, 02 Nov 2015 12:22:26 GMT

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

Re: Pivot Table

marcus_sommer — Mon, 02 Nov 2015 12:22:52 GMT

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