4 Replies Latest reply: Aug 20, 2014 9:48 AM by A Lee

Using string munipulation in Expression

Hello, new user here that would benefit from experts from this forum.

I have a string variable:

vCurrentQuarter = 'Q2'

In my expression, I would like to perform a simple calculation.

For 'Q2', I would like my result to be 'Q1' (i.e. 2-1)

This calculated string of 'Q1' will then be used to pull a value from the cube.

CurrentQuarter.Q1.CV

=sum(={'CurrentQuarter.Q' & (Right('\$(vCurrentQuarter)',1)-1) & '.CV'})

So in essence, I would like my derived line to be:

=sum(CurrentQuarter.Q1.CV)

• Re: Using string munipulation in Expression

Try:

num(Right('\$(vCurrentQuarter)',1))-1

• Re: Using string munipulation in Expression

Hi Michael,

I was always able to get it to resolve to CurrentQuarter.Q1.CV in the Label even with my old statement of

'CurrentQuarter.Q' & (Right('\$(vCurrentQuarter)',1)-1) & '.CV' using without the num function.

But in the expression, in conjuntion of the sum function, Qlikview is not able to retrieve data from the CurrentQuarter.Q1.CV column.

It is how Qlikview expects a non-string variable that is the puzzle that I am trying to solve.

For example:

=sum('CurrentQuarter.Q' & (Right('\$(vCurrentQuarter)',1)-1) & '.CV')

that's equivalent to

=sum('CurrentQuarter.Q1.CV')

which results to nothing.

But if we can somehow make it so it is NOT a string, it would work.

=sum(CurrentQuarter.Q1.CV)

The question is how?

• Re: Using string munipulation in Expression

May be like(magic of '\$'):

=sum(\$(='CurrentQuarter.Q' & (Right('\$(vCurrentQuarter)',1)-1) & '.CV'))