Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a slider in my application which can have a value of 1 - 24.
I'm attempting to use a SET variable in my load script to be loaded into a LET variable.
The slider is called vRunRate.
I'm attempting to pull the date in, which works when using the following expression:
(DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-$(vRunRate)+1,1)),'MMM-YYYY'))
However, if I attempt to use
SET vCurrentPeriodStartDate = "(DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-$(vRunRate)+1,1)),'MMM-YYYY'))";
The value that SET stops at is the $ and $(vRunRate) is never calculated in the LET, so when I use the following LET it's contains no value as show in the screen share:
Here is what my LET statement looks like:
LET eCasesCurrentPeriodRunRate = 'SUM({<CalendarMonthYear= {">=$'&'(=$'&'(vCurrentPeriodStartDate))<=$'&'(=$'&'(vCurrentPeriodEndDate))"},CalendarYear=,CalendarMonth=,CalendarQuarter=> }Cases)';
How can I get the correct value to pass from the SET to the LET? The problem isn't with my LET, it's exists in the SET and passing the $.
I don't understand why you have a double dollar expansion in your second variable expression. In any case there's a dollar expansion in vCurrentPeriodStartDate too that you need to deal with:
SET vCurrentPeriodStartDate = =DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-@(vRunRate)+1,1)),'MMM-YYYY');
LET vCurrentPeriodStartDate = Replace('$(vCurrentPeriodStartDate)','@','$');
LET eCasesCurrentPeriodRunRate = 'SUM({<CalendarMonthYear={">=$' & '(vCurrentPeriodStartDate)<=$' & '(vCurrentPeriodEndDate)"},CalendarYear=,CalendarMonth=,CalendarQuarter= >}Cases)';
Ok,
I was able to get it to work by using the replace on vCurrentPeriodStartDate as you suggested, but I still had to run the syntax for my LET statement above to get it to work.
Here is the final solution:
SET vCurrentPeriodTempStartDate = "(DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-@(vRunRate)+1,1)),'MMM-YYYY'))";
LET vCurrentPeriodStartDate = Replace('$(vCurrentPeriodTempStartDate)','@','$');
LET eCasesCurrentPeriodRunRate = 'SUM({<CalendarMonthYear= {">=$'&'(=$'&'(vCurrentPeriodStartDate))<=$'&'(=$'&'(vCurrentPeriodEndDate))"},CalendarYear=,CalendarMonth=,CalendarQuarter=> }Cases)';
I don't understand why you have a double dollar expansion in your second variable expression. In any case there's a dollar expansion in vCurrentPeriodStartDate too that you need to deal with:
SET vCurrentPeriodStartDate = =DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-@(vRunRate)+1,1)),'MMM-YYYY');
LET vCurrentPeriodStartDate = Replace('$(vCurrentPeriodStartDate)','@','$');
LET eCasesCurrentPeriodRunRate = 'SUM({<CalendarMonthYear={">=$' & '(vCurrentPeriodStartDate)<=$' & '(vCurrentPeriodEndDate)"},CalendarYear=,CalendarMonth=,CalendarQuarter= >}Cases)';
Ok,
I was able to get it to work by using the replace on vCurrentPeriodStartDate as you suggested, but I still had to run the syntax for my LET statement above to get it to work.
Here is the final solution:
SET vCurrentPeriodTempStartDate = "(DATE(MonthStart(AddMonths(CurrentSelectionMonthYear,-@(vRunRate)+1,1)),'MMM-YYYY'))";
LET vCurrentPeriodStartDate = Replace('$(vCurrentPeriodTempStartDate)','@','$');
LET eCasesCurrentPeriodRunRate = 'SUM({<CalendarMonthYear= {">=$'&'(=$'&'(vCurrentPeriodStartDate))<=$'&'(=$'&'(vCurrentPeriodEndDate))"},CalendarYear=,CalendarMonth=,CalendarQuarter=> }Cases)';