topic Re: Sum by Rolling Date in QlikView

Sum by Rolling Date

Fri, 11 Dec 2015 18:29:46 GMT

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";

Re: Sum by Rolling Date

Fri, 11 Dec 2015 18:58:56 GMT

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

Re: Sum by Rolling Date

Anonymous — Fri, 11 Dec 2015 19:11:37 GMT

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

Re: Sum by Rolling Date

Fri, 11 Dec 2015 19:17:29 GMT

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.

Re: Sum by Rolling Date

Fri, 11 Dec 2015 21:06:04 GMT

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

Re: Sum by Rolling Date

Mon, 14 Dec 2015 16:13:11 GMT

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