topic Error in Pivot Table in QlikView

Error in Pivot Table

Tue, 07 Jul 2009 15:41:20 GMT

I am creating a pivot table through a macro after making some selections which act as the dimensions.

The macro works fine when run on the server but when trying to run on IE, the pivot table is created but it is all black in color making it unreadable, even if i send it to excel, the font and the background color is black

Unable to resolve this issue

Please help

Regards

Peter

Error in Pivot Table

Tue, 07 Jul 2009 16:33:27 GMT

Peter,

Are you dynamically setting the background colour? I had something similar recently and as none of the conditions were true QV defaulted it to black. To test if the values are actually there change the fill colour to none for the cells in the exported spreadsheet (if the text colour is black then they are simply being hidden).

Gordon

Error in Pivot Table

Tue, 07 Jul 2009 16:39:35 GMT

Dear Gordon

Yes there is data in the table but since both the background and the text color is black, it is not readable

Please suggext a way where we can change the qv default from black to white

thanks in advance

regards

Peter

Error in Pivot Table

Tue, 07 Jul 2009 17:01:46 GMT

So are you setting the background colour? If you are it sounds like the conditioning is wrong (no colour is being returned) If you want to post the conditioning....

Regards,

Gordon

Error in Pivot Table

Tue, 07 Jul 2009 17:17:36 GMT

No back ground color is set , please find code as below

sub

tabel

set

s=ActiveDocument.Sheets("SH02"

)

for

i=0 to orignumber-1

set

Hlo=ActiveDocument.GetSheet("SH02"

).CreatePivotTable

set

arrslc=ActiveDocument.Fields("$Field"

).GetSelectedValues(200)

for

i=0 to arrslc.Count-1

slc<>"AutoID"

then

end

"sum(if(ValidTo>=[DateFrom] and ValidTo<=[DateTo],(if([Variable1]='BPARate',BPARate,if([Variable1]='BAF',BAF,if([Variable1]='CAF',CAF))))))"

set

cp = Hlo.GetProperties

set

expr = cp.Expressions.Item(0).Item(0).Data.ExpressionVisual

"BPARate"

set

Prop = Hlo.GetProperties

true

end

sub

Error in Pivot Table

Tue, 07 Jul 2009 17:27:30 GMT

I suggest you explicitly set the background colour to white - see BkgColor and BkgColorExp in the API guide.

Regards,

Gordon

Error in Pivot Table

Wed, 08 Jul 2009 11:13:58 GMT

Gordon

Tried using the same but error still persists

Can you send me an example or code ?

Thanks

Peter

Error in Pivot Table

Wed, 08 Jul 2009 15:39:32 GMT

The API guide should show examples, but here is some code I wrote a while ago which you are welcome to pick over. It creates a straight table with dynamic expressions based on the entries in a tablebox and sets the background color based on a numeric entry in the tablebox.

sub CH_TOTALS_Expr
' Set the expressions for the current repairs

set chart = ActiveDocument.GetSheetObject("CH_TOTALS")
set cp = chart.GetProperties

' remove all existing expressions from chart
n = cp.Expressions.Count
Do While n > 0 ' will end when no more expressions or unxepected error from removeexpression
n = chart.RemoveExpression(0)
Loop

' add expression for every repair
set tbox = ActiveDocument.GetSheetObject( "TB_HEADERS" )
For r = 1 to tbox.GetRowCount-1 ' rows numbered from 0 but skip table box header row
set cell = tbox.GetCell(r,2) ' get RBF Code
chart.AddExpression "sum(if ([RBF Code]='" & cell.Text & "', [Reqd Qty], 0))"
Next 'r

' now set the corresponding repair description as the column label and the background colour to the group number
' have to do this as a second loop as after adding an expression would otherwise need to 'GetProperties' each time
set cp = chart.GetProperties
For r = 1 to tbox.GetRowCount-1 ' rows numbered from 0 but skip table box header row
set expr = cp.Expressions.Item(r -1).Item(0).Data 'expressions numbered from 0
set cell = tbox.GetCell(r,3) ' get Repair
set ev = expr.ExpressionVisual
ev.Label.v = cell.Text
ev.ColWidth = 265
set cell = tbox.GetCell(r,0) ' get Group No
set bce = expr.AttributeExpressions.BkgColorExp
bce.Definition.v = "color(" & cell.Text & ")"
Next 'r

cp.TableProperties.StyleNumber = -1 ' set style to 'custom'

chart.SetProperties cp

ActiveDocument.Save

end sub

What happens though after your table is created and you change the background colour manually?

Regards,

Gordon

Error in Pivot Table

Wed, 08 Jul 2009 16:21:43 GMT

Gordon

Thanks for the reply

I have sorted out the problem

Now instead of creating a new pivot table, i am removing the existing fields and adding new fields in already existing Pivot table, this is working fine

regards

Peter

Error in Pivot Table

Mon, 22 Feb 2010 15:37:51 GMT

Hi, I intend to create the same kind of "dynamic" pivot table with a macro to modify dimensions and expressions but i read in the QlikView manual that interaction with objects (using SetProperties for instance) is not allowed as soon as QlikView server is used...

But it seems you implemented it and tested with server. Is this working fine?

Thanks in advance for your feedback.

Error in Pivot Table

Mon, 22 Feb 2010 15:48:04 GMT

You are absolutely right - the manual DOES say you should avoid changing the properties of objects in macros when running the document with QV Server. However, everyone does it and I am not aware of it ever causing any problems.

Having said that, I avoid it whenever possible and indeed you should also avoid macros too if possible (for example create multiple objects where only one is displayed using display conditioning based on variables rather than changing properties).

Regards,

Gordon

Error in Pivot Table

Mon, 22 Feb 2010 16:13:14 GMT

That's interesting... Thank you very much for this answer. I'll rely on you for this "everyone does it" then.

I understand macros should be avoided in order to keep things simple mainly. But official point of view is really scarry as if macros can't be stable or something.

Error in Pivot Table

Mon, 22 Feb 2010 16:20:55 GMT

The main reason is that macros cause inefficiencies - caches are cleared and so on as Qlikview cannot know what you are up to with your code. I have attached a document from Qliktech which should give you more details why macros should be avoided if possible.

Regards,

Gordon

Error in Pivot Table

Fri, 08 Oct 2010 13:14:00 GMT

Hi Peter,

i am facing on exacly the same problem. I want to build a pivot table depending on the selections, my users made on another sheet. There should be different Buttons, where you can choose, how you pivot table should look like and which dimensions and expressions are used. Perhaps you can help me, to find a solution:
How do you remove the existing fields and adding new fields in an already existing Pivot table?

Regards, Martin

Error in Pivot Table

johnw — Sat, 09 Oct 2010 00:21:21 GMT

Not sure quite what you're looking for, but attached is one way of adding and removing dimensions and expressions based on user selections.

Error in Pivot Table

Tue, 19 Oct 2010 12:14:01 GMT

Hi John,

your example helps me to find a solution for my problem. Thank you so much!