Latest 12 weeks of data in script

[#1]

Hi,

I have a QVD wich has last 3 years of data with a weekly granularity. However I dont need last 3 year of data and wanted write an expression in script only which will load latest 12 weeks of data everytime I refresh the dashboard.

My script looks like below:

QUALIFY*;
CI:
LOAD

     GLOBAL_BUSINESS_UNIT as GBU,
     [SUB GBU],
     [SUB GBU-PRODUCT_CATEGORY] as [Product Category],
     left(BUS_AREA_CD,2) as [Product Line],
     C_PLTFRM_SUBSET_NM as [Product Code Name],
     C_HP_PROD_HIER_LVL_05_DN as [Prodoct Name],
     PROD_NO as [Product Number],
     
     FISC_WK_TO_MON_CD as Month,
     Date(FISC_WK_END_DT,'YYYYMMDD') as [Fiscal Week],
     FIS_CAL_WEEK_ID, 
       
     TOTAL_QTY,
     NET_PRICE
FROM

(qvd)

where match (GLOBAL_BUSINESS_UNIT,'IPS','LES') and match(FIS_CAL_WEEK_ID,max(FIS_CAL_WEEK_ID)-12);

Its showing error , how can I get latest 12 weeks in the data. The FIS_CAL_WEEK_ID is a cronological numeric number.

Simky

[shree909]

Hi, Create a temptable and variable

TempDate:

LOAD    

     FIS_CAL_WEEK_ID

(qvd)

Max_Week:

Load (max(FIS_CAL_WEEK_ID)-12) AS Max_Week

resident TempDate;

Let v12weekPrior=peek('Max_Week','0','Max_Week');

Drop table TempDate;

Ur actual script

My script looks like below:

QUALIFY*;
CI:
LOAD

     GLOBAL_BUSINESS_UNIT as GBU,
     [SUB GBU],
     [SUB GBU-PRODUCT_CATEGORY] as [Product Category],
     left(BUS_AREA_CD,2) as [Product Line],
     C_PLTFRM_SUBSET_NM as [Product Code Name],
     C_HP_PROD_HIER_LVL_05_DN as [Prodoct Name],
     PROD_NO as [Product Number],
     
     FISC_WK_TO_MON_CD as Month,
     Date(FISC_WK_END_DT,'YYYYMMDD') as [Fiscal Week],
     FIS_CAL_WEEK_ID, 
       
     TOTAL_QTY,
     NET_PRICE
FROM

(qvd)

where match (GLOBAL_BUSINESS_UNIT,'IPS','LES') and (FIS_CAL_WEEK_ID>='$(v12weekPrior)';

Hope this helps,

if not post a  sample qvw file...

Thanks

[#3 · Accepted solution]

Hi Simky,

12 Weeks of data is roughly 3 months of data.

So

Table:

Load *

From Source

Where Date >= AddMonths(today(),-3) ;

Thanks

AJ

[#4]

It worked. Thanks for the help.