Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Variable String Month Name to Number and back again

Hi,

I'm having a bit of trouble trying to use a user selection variable that is a string. I want to convert that string to a number, use the number to add 1 to it then turn it back into a string to query with. Basically we want to "forcast" the next months goals by looking at what the user selected.

Here is what I have so far it seems like it should work but I'm just getting "Zero" back.

vMonthSelection  is defined as:

=GetFieldSelections([Month])

(MAX({<[Monthly Goals.mon] = {(=vMonthSelection)}>}[Monthly Goals.nmbr mon]) + 1)

Returns a 4. when the user selects Jan, Feb, Mar.

SUM({< [Monthly Goals.nmbr mon] = {4} >}[Monthly Goals.dollar goal])

Returns the goal for the 4th month.

However

SUM({< [Monthly Goals.nmbr mon] = {MAX({<[Monthly Goals.mon] = {(=vMonthSelection)}>}[Monthly Goals.nmbr mon]) + 1)} >}[Monthly Goals.dollar goal])

Returns ZERO.


So I'm not sure what I'm doing wrong, I don't get any syntax errors either.


Any help would be wonderful.


Thanks

1 Solution

Accepted Solutions

Re: Variable String Month Name to Number and back again

You need to use dollar expansion to evaluate the max expression. And you can simply use MAX(Month)

SUM({< [Monthly Goals.nmbr mon] = {$(=MAX(Month) + 1)} >}[Monthly Goals.dollar goal])

3 Replies

Re: Variable String Month Name to Number and back again

You need to use dollar expansion to evaluate the max expression. And you can simply use MAX(Month)

SUM({< [Monthly Goals.nmbr mon] = {$(=MAX(Month) + 1)} >}[Monthly Goals.dollar goal])

Re: Variable String Month Name to Number and back again


SUM({< [Monthly Goals.nmbr mon] = {"$(=vMonthSelection) + 1"} >}[Monthly Goals.dollar goal])

MVP
MVP

Re: Variable String Month Name to Number and back again

HI,

Try like this

SUM({< [Monthly Goals.nmbr mon] = {$(=MAX(Month) + 1)} >}[Monthly Goals.dollar goal])


Regards,

Jagan.