topic Re: Using string munipulation in Expression in QlikView

Using string munipulation in Expression

Tue, 19 Aug 2014 21:03:51 GMT

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 — Tue, 19 Aug 2014 21:11:11 GMT

Try:

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

Wed, 20 Aug 2014 12:48:49 GMT

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?

tresB — Wed, 20 Aug 2014 12:53:18 GMT

May be like(magic of '$'):

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

Wed, 20 Aug 2014 13:46:55 GMT

This is great!

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

Thanks again to whoever that helped out.