10 Replies Latest reply: Feb 7, 2017 12:15 AM by Jonathan Dienst RSS

    Issue with storing date as a variable to be used in set analysis

    Melvin Ling

      I have created a variable to store the prior month that user selects.

      vPriorMonth =  Date(addmonths(MonthStart(SnapDate),-1),'MMM-YY')

       

      Purpose is to show the previous month's balance if a month is selected.

      eg. User select SnapDate = Jul-16

       

      The following expression will generate these outputs

       

      $(vPriorMonth)  => Jun-16

       

      Sum({$<SnapDate=, SnapDate={'Jun-16'}>}MonthEndBalance) => The correct balance if I hardcoded it

       

      Sum({<SnapDate=, SnapDate={'$(vPriorMonth)'}>}MonthEndBalance)  => No results

      Sum({<SnapDate=, SnapDate={$(=vPriorMonth)}>}MonthEndBalance)  =>  => No results

       

      I have narrowed it down to the issue is that it is a date field as I have replaced the date check with a unrelated check and the code works fine.

       

      Is there any way to get the set analysis to work?

       

      Additional info, not too sure if it helps.

      The date format in the load editor:

      SET DateFormat='DDMMMYYYY';

       

      How SnapDate is loaded:

      Date(MonthStart(SNAP_DATE),'MMM-YY') AS SnapDate