topic Pivot Table in QlikView

Pivot Table

NavinReddy — Tue, 01 Apr 2014 15:42:36 GMT

Hi All,

I have one pivot table

When i select any value in list box ,if value is null mean The table size should become small

how can i make it table should be same size ,if it value '0' also pivot should be same size

Please find the image

Thanks In Advance

Niru

Re: Pivot Table

giakoum — Tue, 01 Apr 2014 15:44:39 GMT

replace null with... something

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 15:53:27 GMT

Hi,

I tried but im getting same

Thanks you

Regards,

Niru

Re: Pivot Table

giakoum — Tue, 01 Apr 2014 15:55:35 GMT

not sure what you are trying to achieve but deselect "Size to Data" in the layout and create a show condition : if there is no data, display some message.

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 16:06:57 GMT

Thanks you its solve half of the problem

Where can i write the missage if there is no data mean

Re: Pivot Table

giakoum — Tue, 01 Apr 2014 16:08:12 GMT

Error messages in the general tab

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 16:13:13 GMT

Yes but there are many option which option can i select

Re: Pivot Table

giakoum — Tue, 01 Apr 2014 16:17:48 GMT

Default or calculation condition unfulfilled in your case

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 16:28:58 GMT

How can i write i tried but its not showing message

Re: Pivot Table

giakoum — Tue, 01 Apr 2014 16:30:58 GMT

It is a normal expression box. If you have a sample app I can help.

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 16:33:00 GMT

yes but its confidential data i cant

give me expression example

Re: Re: Pivot Table

giakoum — Tue, 01 Apr 2014 16:46:25 GMT

here you go

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 19:32:37 GMT

Hi,

in list box i have some fields '0' value

if i click that field in list box , pivot table have to show 'No data'

how can i do this some one guide me

Regards,

Niru

Re: Pivot Table

Tue, 01 Apr 2014 19:46:12 GMT

Could you try to map between the 2 tables for '0' value as 'No data' in the script and simply fetch the field in the pivot table.

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 19:50:38 GMT

Hi,

How can i will you please explain me

Regards,

Niru

Re: Pivot Table

Tue, 01 Apr 2014 20:15:33 GMT

1) Check the relation between listbox field say A with pivot table field B

2) In script whenever A = 0; then B= 'No value'- use applymap

eg:

Tab1:

Mapping load

key,

tab2:

Field1,

Field2,

Applymap('Tab1',B,if(A==0,'No Value',B) as B

3)Reload A,B with other fields and fetch the fields in pivot table and list box and check it out

I am not sure about the relation between the fields in detail, but this another way of doing

Re: Pivot Table

NavinReddy — Tue, 01 Apr 2014 20:24:05 GMT

Hi,

Sorry im totally confused City Name is the list box

if New York=0,

How can i show in pivot table No Data

Please guide me

Regards,

Niru

Re: Pivot Table

Tue, 01 Apr 2014 20:43:42 GMT

you can use GetFieldSelections to fetch the exact field value 'New york' from City Name and use the same expression in the apply map.

Re: Pivot Table

stevedark — Tue, 01 Apr 2014 22:41:07 GMT

If you want to show zeros you may be able to achieve this by checking include zeros on the Presentation tab, and then alter your expression to include a set that ignores selections, eg:

=sum(Value) + (sum({1}Value) * 0)

It's a bit of a fudge, but {1} in the expression is Set Analysis syntax for ignore selections. You will then get a value for all dimensions - regardless of selections. However, this value is removed by multiplying it by zero.

If you would rather just hide the pivot and show a message instead you can use Conditional Shows. Place a conditional show on the pivot (on the Layout tab) of:

=sum(Value) <> 0

Obviously the expression will need to match the expression in your Pivot. You can then create a Text Area with a message such as 'No Values Found For the Current Selection', and set a corresponding Show Condition on that also:

=sum(Value) = 0

If you have both positive and negative values and there is a risk that positives in one dimension will be netted off by negatives in another you may need to use an AGGR statement - but don't worry about this if you don't have to.

Hope this points you in the right direction.

On using Show/Hide conditions, you may find this example document useful:

QlikView App: At The Qlik Of A Button

Steve