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
