CODEWITHSUNDEEP
chartsreferencescriptingqlikview
kiriloff
kiriloff

Reputation: 26333

QLikVIew reference to a Calculated Dimension in another Calculated Dimension

Is it possible to refer to a Calculated Dimension in another Calculated Dimension in a Pivot Table ? Can ValueList() be used for this usage, and what would be the appropriate syntax for it ?

I cannot use the script for this, and I cannot create a new variable either.

I cannot copy the whole expression for the first calculated dimension in the expression for the second calculated dimension as both expression are very expensive (pick(match(...)).

Thanks

Upvotes: 3

Views: 5096

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

Unfortunately, there is no obvious way to refer to other calculated dimensions in other calculated dimensions. As you mention that you are restricted to creating an expression solution only, then my other suggestion of using a variable is not applicable in this case.

Therefore it appears that you have to repeat the calculated dimension.

I would say that if you truly have to use an expression solution then it may be worth trying to optimise your calculated dimension as much as possible so that when you have to repeat it in your document it should have a negligible extra impact on performance.

ValueList is useful for generating dimension values, but I do not think it could help you here as you still cannot refer to other calculated dimensions using it. It is really designed for generating values for calculated dimensions, and has the following syntax:

ValueList(value {, value })

So for example, I could use:

=ValueList('Category A','Category B','Category C')

If I put it into a Pivot Table chart, I would obtain:

QlikView Table demonstration of ValueList

As you can see, the value of your expression is repeated for each entry provided by ValueList. However, ValueList has a nice trick up its sleeve which means that you can also use it in an expression to display different values depending on the dimension value it appears against.

So for example, if I used the following as my table's expression:

=if(ValueList('Category A','Category B','Category C')='Category A',
    sum(Value * 10),
    if(ValueList('Category A','Category B','Category C')='Category B',
        sum(Value * 100),
        sum(Value * 1000)
      )
    )

Here you can see that I added a test to see if we were in "Category A" and then displayed the value sum(Value * 10) and then for "Category B", sum(Value * 100) and so on, this results in:

QlikView Table demonstration of ValueList in the expression

Upvotes: 2

Related Questions