3 Replies Latest reply: Mar 22, 2016 4:13 AM by jagan mohan rao appala RSS

    Variable String Month Name to Number and back again

    Derek Hutchinson

      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