Sum by Rolling Date

[#1]

I need to do a sum based on DAY_COD which is YYYYMM.  I need to sum up the last six months rolling grouped in to it's primary key

I am assuming I need to do six different lines of script, basically a sum for each DAY_COD-1, DAY_COD-2, DAY_COD-3,DAY_COD-4,DAY_COD-5,DAY_COD-6?  This is for a QVD creation.

I have the following but it does not seem to be working. Any help would be greatly appreciated.

LOAD
CTL_ARE_COD,
left(DAY_COD,4) as YEAR,
mid(DAY_COD,5,2) as MONTH,
Date(left(DAY_COD,4))&Date(mid(DAY_COD,5,2)) as DAY_COD,
LCL_CUR_COD,
MAT_COD,// as %Product,
PDN_BAS_UNT_QTY,// as TotalVolume,
PDN_ORD_NUM

SKU_UOM_COD;

TMP2:
LOAD
YEAR&MONTH&"MAT_COD" as %CalProdKey,
//"DAY_COD",
"MAT_COD",
//"PDN_BAS_UNT_QTY" as TotalVolume,
sum(if(DAY_COD-6,"PDN_BAS_UNT_QTY")) as TotalVolume6,//This is the code piece I need to get working
sum(if(DAY_COD-5,"PDN_BAS_UNT_QTY")) as TotalVolume5,//This is the code piece I need to get working

sum(if(DAY_COD-4,"PDN_BAS_UNT_QTY")) as TotalVolume4,//This is the code piece I need to get working

sum(if(DAY_COD-3,"PDN_BAS_UNT_QTY")) as TotalVolume3,//This is the code piece I need to get working

sum(if(DAY_COD-2,"PDN_BAS_UNT_QTY")) as TotalVolume2,//This is the code piece I need to get working

sum(if(DAY_COD-1,"PDN_BAS_UNT_QTY")) as TotalVolume1,//This is the code piece I need to get working

"PLT_COD",
"SKU_UOM_COD"
Resident TMP
Group by
YEAR,
MONTH,
"MAT_COD",
"PLT_COD",
"SKU_UOM_COD";

[#2]

Hi Jonathan, you can try with this:

LOAD

CTL_ARE_COD,

year(date#(DAY_COD, 'YYYYMM') )as YEAR,

month(date#(DAY_COD, 'YYYYMM'))as MONTH,

Date(date#(DAY_COD , 'YYYYMM')) as DAY_COD,

LCL_CUR_COD,

MAT_COD,// as %Product,

PDN_BAS_UNT_QTY,// as TotalVolume,

PDN_ORD_NUM

SKU_UOM_COD;

The date# function interpretates the dateformat as you have it, and with the date() function you gives the format that you wanna it..

I hope that it help you

I attach an example that you can see !!

Regards

[Anonymous]

What are you hoping this will do ?

     Date(left(DAY_COD,4))&Date(mid(DAY_COD,5,2)) as DAY_COD,

When I put this expression in a text box :

     =Date(left('201508',4))&Date(mid('201508',5,2))

it returned :      07/07/190507/01/1900

I'd suggest you convert your DAY_COD to a QlikView date before manipulating it :

     =Date(Date#('201508','YYYYMM'),'DD/MM/YYYY')

Then you can use QlikView Date functions against it, like :

     =Year ( Date(Date#('201508','YYYYMM'),'DD/MM/YYYY') )

     =Month ( Date(Date#('201508','YYYYMM'),'DD/MM/YYYY') )

     =WeekDay ( Date(Date#('201508','YYYYMM'),'DD/MM/YYYY') )

     =num ( WeekDay ( Date(Date#('201508','YYYYMM'),'DD/MM/YYYY') ) )

[#4]

I will make that change, do you have any suggestions on how to do the sum if statements?  Basically I want to take those six months and then combine them to equal one month total.  But I need to get each line working.

[#5]

I will simplify the question, I need to do a 6 month rolling sum based on date

DAY_COD is date

PDN_BAS_UNT_QTY is qty

[#6]

OK, so I got the date working, any idea of how to calculate the Sum based by DAY_COD minus how many ever months I need, my script is not pulling any results.  This example would be for say, six months ago as this month.

YEAR&MONTH&"MAT_COD" as %CalProdKey,
"MAT_COD",
Date#(date(DAY_COD,'YYYYMM')) as DAY_COD,
sum(if(Date#(date(DAY_COD,'YYYYMM'))-6,"PDN_BAS_UNT_QTY",)) as TotalVolume