topic Re: Pivot Table / Selection explanation in QlikView

Pivot Table / Selection explanation

Thu, 27 Feb 2014 14:58:14 GMT

Hi! I create a pivot table. I need an explanation about:

1) How can I hide the records where 2011 fields are null/zero?

2) How can I show only records where department fields are C or D?

You can find an example attached.

Any suggestions greatly appreciated.

Andrea

Re: Pivot Table / Selection explanation

alexandros17 — Thu, 27 Feb 2014 15:02:52 GMT

1) To hide a record yoou need to have all the values on the row set to zero, so in other columns you must test:

If(column(1) = 0, 0, expression) in this way you can go in tab presentation (properties of the object) and check "set all values to zero"

2) in dimension write:

if(department = 'C' or department='D', department, null())

and check the "suppress when value is null"

Hope it helps

Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 15:43:42 GMT

Can you help me with an example pls?

Re: Pivot Table / Selection explanation

MK_QSL — Thu, 27 Feb 2014 15:55:26 GMT

Instead of Department use below as calculated dimension and tick suppress when value is Null

=IF(Department = 'C', Department,

If(Department = 'D', Department))

Re: Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:04:43 GMT

Similar to what Manish explained. See attached.

Thanks

Re: Re: Pivot Table / Selection explanation

alexandros17 — Thu, 27 Feb 2014 16:08:03 GMT

for the second point just copy this: if(department = 'C' or department='D', department, null()) and paste in the dimension ... then check the box as I said before

for the first point, in all the expression after the first write:

If(column(1) = 0, 0, myexpression) and change myexpression with the expression you have written in the column

Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:08:58 GMT

Thank you for you help!

Can you show me how can i hide the 2011 fields where the value is zero, pls?

Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:13:36 GMT

Thank you for you help!

I try to hide all the values set to zero in the example attached, but I don't know where I made a mistake.

Can you take a look pls?

Re: Re: Pivot Table / Selection explanation

alexandros17 — Thu, 27 Feb 2014 16:14:15 GMT

Unfortunately I can't see any attachment ...

Re: Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:18:31 GMT

Here it is

Re: Re: Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:19:05 GMT

Attached with rows suppressed when Apha 2011 is zero/null.

Re: Pivot Table / Selection explanation

alexandros17 — Thu, 27 Feb 2014 16:20:46 GMT

Sorry,

I didn't see before,

here is your example modified;

obviously if you need to check all columns of 2011 you will have to extend the condition, something like

If(column(1)=0 and column(2)=0

And so on ...

Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:26:33 GMT

Thank you so much for your help!

Re: Pivot Table / Selection explanation

Thu, 27 Feb 2014 16:27:16 GMT

Thank you so much for your help!