Using string munipulation in Expression

[#1]

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)

I cannot get it to work unfortunately, please help.

[m_woolf]

Try:

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

[#3]

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?

[tresesco · Accepted solution]

May be like(magic of '$'):

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

[#5]

This is great!

I ended up reading more about this $-sign expansion here for the other people that got stumped.

Q-Tip #6 - Those Tricky $-Sign Expansions | Natural Synergies

Thanks again to whoever that helped out.