Sum based upon Date

[#1]

Hi, I need to create a sum based upon previous months so I need to do Date formatted as 'YYYYMM' and sum QTY but sum the previous months, so I need to do Date-1 months, Date-2 months, etc.

Can someone please assist, I am struggling with this one.

Fields: Date, Qty

and this would be going in to a QVD, so I can't do set analysis in an object.

[Gysbert_Wassenaar]

It's not clear to me what you want. Can you post a small qlikview document that explains what you're trying to do?

[JonnyPoole]

It sounds like you need to refer to earlier dates for N number of periods.   If Set Analysis and range/inter record functions won't work for you , then the last option is the As-Of table solution in the data model where you associate the chart values to N number of preceding date buckets.

The As-Of Table

[#4]

Basically I need to do a rolling sum of the past six months.  I figured I could tell it DAY_COD minus and then combine all the totals in to one to get my volume.  Does that make sense?  Or if there is another way to do this please let me know, this one is getting me really fustrated.

YEAR&MONTH&"MAT_COD" as %CalProdKey,
"MAT_COD",
Date#(date(DAY_COD,'YYYYMM')) as DAY_COD,
sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY')-5,"PDN_BAS_UNT_QTY",)) as TotalVolume6,

sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY')-4,"PDN_BAS_UNT_QTY",)) as TotalVolume5,

sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY')-3,"PDN_BAS_UNT_QTY",)) as TotalVolume4,

sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY')-2,"PDN_BAS_UNT_QTY",)) as TotalVolume3,

sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY')-1,"PDN_BAS_UNT_QTY",)) as TotalVolume2,

sum(if(Date#(date(DAY_COD,'YYYYMM'),'MM/DD/YYYY'),"PDN_BAS_UNT_QTY",)) as TotalVolume1,

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

[#5]

How would I set this AsOfTable up to just see six months rolled up and not 12? If I select a date a year ago I need to see the previous six months from that date rolled up in to one.

[JonnyPoole]

Here is an example that i did from scratch.

It will change.

It will change depending on the format of your dates but the approach is the same

[HirisH_V7]

Hi,

Here are the flags you can create ,

MTD:	=sum({<Date={'$(=max(Date))'},%Flag_ThisMonthToDate={1}>} Sales)
Previous MTD:	=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonthComparative_M01={1}>} Sales)
Previous Month:	=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M01={1}>}Sales)
Same Month Last Year:	=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M12={1} >}     Sales)

Hope this Helps,

Or

Possible post some sample data

Regards,

Hirish

[#8]

Jonathan,

OK this is getting me there I think, I am having some formatting issues with date since it is coming in, in an unfriendly format.  I think this is my last stumbling block.  Here is my entire script, if you can help I would really appreciate it.

The date is coming in as YYYYMM. I got the date working for the YearMonth, in the script you posted but getting them to come is as year and month I am doing something wrong.  Thanks ahead of time for your help, this one is kicking my butt.

//-------- Start Multiple Select Statements ------
TMP:
LOAD
left(DAY_COD,4) as YEAR,
mid(DAY_COD,5,2) as MONTH,
Date(date#(DAY_COD,'YYYYMMDD')) as DAY_COD,
    MAT_COD,// as %Product,
    PDN_BAS_UNT_QTY,// as TotalVolume,
    PLT_COD,
    SKU_UOM_COD
  WHERE MATCH(PLT_COD,'0155','0158','0162');
//-------- End Multiple Select Statements ------

TMP2:

LOAD
YEAR,
MONTH,
"MAT_COD",
Date#(date(DAY_COD,'YYYYMM')) as DAY_COD,
sum("PDN_BAS_UNT_QTY") as TotalVolume,
"PLT_COD"
Resident TMP
Group by
YEAR,
MONTH,
"MAT_COD",
"PLT_COD",
"DAY_COD";
left join
R_MAT_MEA:
LOAD
MAT_COD,
CNV_VAL,
NET_WGH_VAL
FROM
[R_MAT_MEA.QVD]
(qvd)
Where Match(ALT_UNT_COD,'CAR');

AsofCalendar:

Load
Year(Date#(date(DAY_COD,'YYYYMM'))  ) as Year,
DAY_COD as YearMonth,
Month(Date#(date(DAY_COD,'YYYYMM'))  ) as Month,
TotalVolume;
Load
DAY_COD,
MAT_COD,
PLT_COD,
TotalVolume
Resident TMP2;

let vAsOfYears=6;
for i=1 to $(vAsOfYears)

AsOfDate:
Load
//Year(addmonths(date#(YearMonth,'YYYYMM'), 1-$(i)))*100 + Month(addmonths(date#(YearMonth,'YYYYMM'), 1-$(i))) as YearMonth,
Year(addmonths(date(YearMonth,), 1-$(i)))*100 + Month(addmonths(date(YearMonth,), 1-$(i))) as YearMonth,
YearMonth as AsOfYearMonth,
Month as AsOfMonth,
Year as AsOfYear
Resident AsofCalendar;
next i; 

[#9]

Correction to my statement DAY_COD format is coming in as YYYYMMDD.

[#10]

Hirish,

This is for a QVD creation so I don't think the Set Analysis Flags will work for me, I have to do it in the script, unless I am not understanding your post correctly.