topic Re: SET Variable in load script issue with $ in QlikView

SET Variable in load script issue with $

NickHoff — Mon, 24 May 2021 18:44:30 GMT

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 $.

Re: SET Variable in load script issue with $

Gysbert_Wassenaar — Mon, 24 May 2021 17:46:38 GMT

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)';

Re: SET Variable in load script issue with $

NickHoff — Tue, 25 May 2021 13:55:49 GMT

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)';