0 Replies Latest reply: Sep 13, 2018 9:15 PM by haifeng zhao RSS

    Variable Concerns

    haifeng zhao

      Dear all,

       

      Let me explain my case as below,

       

      Now we want to set the latest date based on different country, so we set in the script like this:

      LOAD 

          Max(TIME_KEY) as Asofdates,

        DATE(MonthStart(DATE(Date#(max(TIME_KEY),'YYYYMMDD'),'MM/DD/YYYY')),'YYYYMMDD') as MonthBegin,

          DATE(MonthStart(DATE(Date#(max(TIME_KEY),'YYYYMMDD'),'MM/DD/YYYY'),-12),'YYYYMMDD') as MonthBegin_lastyear,

          Upper(AFFILIATE_CODE) as COUNTRY

          FROM [lib://SM1/01_QS_Documents\Sales.QVD](qvd)

         group By AFFILIATE_CODE;

       

      So we set variable

      v_Daily_MonthBegin=(MonthBegin)

      v_Daily_MonthBegin_lastyear=(MonthBegin_lastyear)


      Then we want to select the latest 12 month, so use script below in the table and filter

      =if(I_Time_Key>v_Daily_MonthBegin_lastyear and I_Time_Key<= v_Daily_MonthBegin,I_Time_Key),


      but the scope not changed, always based on one country no matter what country we select,  also the variable will update when we select country. Please give me your suggestion how to achive the function? Thanks.


      Best Regards,

      Haifeng