Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community !
In a lot of my chart I use AGGR fonction like :
Sum(
Aggr(
Sum(Measure),
DimX,
Dim1,
Dim2
)
)
DimX is in all expressions and Dim1/Dim2/.../DimN not.
example :
Dim1 | Dim2 | Expression |
---|---|---|
A | BB | 12 |
Problem : I would like to create a variable with the list of the chart dimensions (Dim1, Dim2 in my example).
Thanks
I think you could reach a better performance if you outsorced this expression into a variable - getobjectfield had a second parameter for the objectid - then the calculation should be only done once and not for each row in the chart. With this you could spare the if-loops and prepare this string with text-functions like replace/left/right ... in this kind that a valid dimension-list would be returned which could be global used in many expressions:
vDimList:
=
GetObjectField(0, $1) & ', ' &
GetObjectField(1, $1) & ', ' &
GetObjectField(2, $1) & ', ' &
GetObjectField(3, $1) & ', ' &
GetObjectField(4, $1) & ', ' &
GetObjectField(5, $1)
Sum(
Aggr(
Sum(Measure),
$(=vDimList('CH01'))
)
)
I think the syntax will be actually not quite correct and needs some adjustments but it should work. Perhaps you used a second parameter for these variable with them the dimension-list would be cutted after n-dimensions or ...
GetObjectField(0, $1, $2)
$(=vDimList('CH01', 2)
- Marcus
I think you could reach a better performance if you outsorced this expression into a variable - getobjectfield had a second parameter for the objectid - then the calculation should be only done once and not for each row in the chart. With this you could spare the if-loops and prepare this string with text-functions like replace/left/right ... in this kind that a valid dimension-list would be returned which could be global used in many expressions:
vDimList:
=
GetObjectField(0, $1) & ', ' &
GetObjectField(1, $1) & ', ' &
GetObjectField(2, $1) & ', ' &
GetObjectField(3, $1) & ', ' &
GetObjectField(4, $1) & ', ' &
GetObjectField(5, $1)
Sum(
Aggr(
Sum(Measure),
$(=vDimList('CH01'))
)
)
I think the syntax will be actually not quite correct and needs some adjustments but it should work. Perhaps you used a second parameter for these variable with them the dimension-list would be cutted after n-dimensions or ...
GetObjectField(0, $1, $2)
$(=vDimList('CH01', 2)
- Marcus
Thank you Marcus.
In all my expressions I would like to use just one variable and without parameters (it's easier to maintain).
I have tried :
concat(distinct GetObjectField(ValueList(0,1, 2, 3, 4)), ', ')
or
concat(distinct GetObjectField(SubField('0_1_2_3_4', '_')), ', ')
That work with a little sample of data (like the QlikView script test), but no in my app (50 000K of lines).
Aurélien
I don't know a better way as to use an external variable. You don't need to use the parametrized variable-version but if you have more then one expression it will reduce a lot the effort for maintaining.
- Marcus