Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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)

I cannot get it to work unfortunately, please help.

1 Solution

Accepted Solutions
MVP
MVP

Re: Using string munipulation in Expression

May be like(magic of '$'):

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

4 Replies
mwoolf
Honored Contributor II

Re: Using string munipulation in Expression

Try:

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

Not applicable

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?

MVP
MVP

Re: Using string munipulation in Expression

May be like(magic of '$'):

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

Not applicable

Re: Using string munipulation in Expression

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.

Community Browser