long expression string in dynamic chart and Macro

Wed, 21 Nov 2012 10:07:57 GMT

Hi all,

in a QV app, I have a dynamic chart, a list of dimensions and expressions set in the script like this :

Dimensions:

Load * inline [

%DimName, %Dimension

Code adhérent, "Code adhérent"

Code assureur, "Code assureur"

Nom assureur, "Nom assureur"

Type adhérent, "Type adhérent"

Exercice technique, "Exercice technique"

Contrat, "Contrat"

Offre, "Offre"

Structure, "Entité"

Garantie commerciale, "Garantie commerciale"

Garantie technique, "Garantie technique"

Exercice comptable, annee_cri

Mois comptable, num_mois_cri

Date CRI, DATE_CRI

];

Expresions:

load * inline [

%ExpName, %Expression

Montant, sum(MTECR_SOLDE)

Montant taxes, Sum({$<TYPE_ECRITURE_VT={TVA,TCA,CGA,CMU}>}MTECRVT_SOLDE)

Montant ristourne, Sum({$<TYPE_ECRITURE_VT={RIST,RISTA,REPRA}>}MTECRVT_SOLDE)

];

the chart works fine with alle dimension and wuth the first expression but if i set a tab with the expression "Montant ristourne" i've in the expression only : "Sum({$<TYPE_ECRITURE_VT={RIST" the string is cut...

here is the macro code : i don't understand what's wrong...

thanks for your help !

Dim vDim

Dim vExp

sub onAnySelection

set x = ActiveDocument.GetCurrentSelections

s = x.Selections

v = x.VarId

bDim = false

bExp = false

for i = lbound(s) to ubound(s)

'msgbox v(i)&" = "&s(i)

if v(i) = "%Dimension" then

bDim = true

vDim = s(i)

end if

if v(i) = "%ExpName" then

bExp = true

vExp = s(i)

end if

next

if bDim and bExp then

buildChart

end if

end sub

sub buildChart

set chart = ActiveDocument.GetSheetObject("TEST")

'première étape : on vide le tableau

set dims = chart.GetProperties.Dimensions 'on récupère les dimensions

set exps = chart.GetProperties.Expressions 'on récupère les expressions

'msgbox("Dimensions = " & dims.Count)

'msgbox("Expressions = " & exps.Count)

for i=0 to dims.Count 'on boucle X fois pour supprimer les X dimensions présentes

chart.RemoveDimension 0

next

'add dimensions

set dimSelection = ActiveDocument.fields("%Dimension").GetSelectedValues

for i=0 to dimSelection.Count - 1

'msgbox(dimSelection.Item(i).text)

chart.AddDimension dimSelection.Item(i).text

next

for i=0 to exps.Count - 1 'on boucle X-1 fois pour supprimer les X-1 dimensions présentes pourquoi "-1" ?????

chart.RemoveExpression 0

next

set expValues = ActiveDocument.fields("%Expression").getPossibleValues

set expSelection = ActiveDocument.fields("%ExpName").GetSelectedValues

for i=0 to expValues.Count - 1

'msgbox(expValues.Item(i).Text)

chart.AddExpression expValues.Item(i).Text

'rename an expression label

set p = chart.GetProperties

set exps = p.Expressions

set expItem = exps.Item(i).Item(0).Data.ExpressionVisual

expItem.Label.v = expSelection.Item(i).Text

'msgbox(expItem.Label.v)

chart.SetProperties p

next

end sub

PS : My work is based on http://community.qlik.com/qlikviews/1077

i'm in V11 SR1

Re: long expression string in dynamic chart and Macro

Fri, 23 Nov 2012 08:30:13 GMT

I note that's not a string lenght trouble. the trouble is the coma in the expressoin, in all my test, the expression stop juste before the firts coma...

So i search about escape character in qv and i read this topic : http://community.qlik.com/message/118416#118416

I change nothing into the macro but in my script, i've modified " Montant ristourne, Sum({$<TYPE_ECRITURE_VT={RIST,RISTA,REPRA}>}MTECRVT_SOLDE)" into "
Montant ristourne, Sum({$<TYPE_ECRITURE_VT={RIST}>}MTECRVT_SOLDE)+
Montant ristourne, Sum({$<TYPE_ECRITURE_VT={RISTA>}MTECRVT_SOLDE)+
Montant ristourne, Sum({$<TYPE_ECRITURE_VT={REPRA}>}MTECRVT_SOLDE)

it's working... but this could not working with set analysis more complex 😞

topic Re: long expression string in dynamic chart and Macro in QlikView

long expression string in dynamic chart and Macro

Re: long expression string in dynamic chart and Macro