topic Re: Dynamic pivot table column width in QlikView

Dynamic pivot table column width

Fri, 21 Dec 2012 11:08:50 GMT

Dear Sirs

I have a pivot table with DATE as the horizontal dimension like a calender.

I wish to develop a macro which dynamically change the column width according to the number of dates that the user selects.

The goal is that the table always has the same total width of 2000 pixels (if more dates are selected => columnwidth narrows and vice versa)

E.g.:

-if 91 days are selected => columnwidth = 2000/91

-if 31 days are selected => columnwidth = 2000/31

-minimum colum width = 20

-maximum column width = 200

Can anyone help with some macro skills? I believe I also have to define a variable like [count(distinct Date)] and somehow apply that variable in the macro.

Merry xmas to all!

Jesper

Re: Dynamic pivot table column width

Gysbert_Wassenaar — Fri, 21 Dec 2012 12:04:35 GMT

This does what you asked for, but you'll probably end up with unreadable headers. Vertical headers seem to need about the same space as horizontal headers.

Sub

set val=ActiveDocument.Fields("Date").GetSelectedValues

ColCount = val.count

ColWidth = 2000/ColCount

if ColWidth > 200 then ColWidth = 200

if ColWidth < 20 then ColWidth = 20

set chart = ActiveDocument.GetSheetObject( "CH01" )

chart.SetPixWidth val.count,ColWidth

end sub

Re: Dynamic pivot table column width

Fri, 21 Dec 2012 12:11:26 GMT

Thanks, I cant get it to work I think this line in the macro is the problem:

ColWidth = 2000/ColCount

Do I need () og [] or somthing?

Re: Dynamic pivot table column width

Fri, 21 Dec 2012 12:16:42 GMT

Or: is the problem that the selections are not made in the DATE field but in the YEAR and MONTH field? (with underlying date values)

Re: Dynamic pivot table column width

Fri, 21 Dec 2012 12:18:36 GMT

It was - i nailed it 🙂

Thanks mr. W

🙂

Re: Dynamic pivot table column width

Fri, 21 Dec 2012 13:24:50 GMT

In addition:

Is it possible to develop a macro, that reads the total width of the pivot table (e.g. 2.200 pixels)

- deducts the column width of columns 1 and 2 (e.g. 150 pixels)

- uses the remaining 2.050 pixels in the above stated macro formula

Re: Dynamic pivot table column width

Gysbert_Wassenaar — Fri, 21 Dec 2012 13:43:23 GMT

Try:

Sub MySub

set val=ActiveDocument.Fields("Date").GetSelectedValues

ColCount = val.count

set chart = ActiveDocument.GetSheetObject( "CH01" )

pwidths = chart.GetPixWidths(0,2).PixWidths

set fr = chart.GetFrameDef

set pos = fr.Rect

restwidth = pos.Width - pwidths(0) - pwidths(1)

ColWidth = restwidth/ColCount

if ColWidth > 200 then ColWidth = 200

if ColWidth < 20 then ColWidth = 20

chart.SetPixWidth val.count,ColWidth

end sub

Re: Dynamic pivot table column width

Sat, 22 Dec 2012 15:46:15 GMT

For some reason i need to convert doc units to pixels so this one works.

Thanks.

set val=ActiveDocument.Fields("RessourceStartdato").GetPossibleValues
ColCount = val.count

set chart = ActiveDocument.GetSheetObject("CH71")

pwidths = chart.GetPixWidths(0,2).PixWidths
set fr = chart.GetFrameDef
set pos = fr.Rect

restwidth = pos.Width/3.125 - pwidths(0) - pwidths(1) - 50

ColWidth = restwidth/ColCount
if ColWidth > 200 then ColWidth = 200
if ColWidth < 20 then ColWidth = 20
chart.SetPixWidth val.count,ColWidth

Re: Dynamic pivot table column width

orital81 — Wed, 31 Jul 2013 06:17:40 GMT

Hi Gysbert, this is an old discussion but relevant for me right now.

Im using your Macro suggestion, it's working great except when I clear selection.

When clearing selection the macro fails (I guess since ColCount= 0 and we devide by 0)

Im trying to overcome this by using GetPossibleCount in a variable and sending it to the function

but I cannot get it working..

I will appreciate your help here. See attached file.

CAdjust1 is using the variable while CAdjust is without (doesn't work when I clear all)

Thanks in advance

Ori