topic Re: Sum based upon Date in QlikView

Sum based upon Date

Mon, 14 Dec 2015 18:30:02 GMT

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.

Re: Sum based upon Date

Gysbert_Wassenaar — Mon, 14 Dec 2015 18:44:06 GMT

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

Re: Sum based upon Date

JonnyPoole — Mon, 14 Dec 2015 18:49:13 GMT

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

Re: Sum based upon Date

Mon, 14 Dec 2015 19:08:10 GMT

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"

Re: Sum based upon Date

Mon, 14 Dec 2015 21:38:05 GMT

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.

Re: Sum based upon Date

JonnyPoole — Tue, 15 Dec 2015 04:12:44 GMT

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

Re: Sum based upon Date

HirisH_V7 — Tue, 15 Dec 2015 05:19:04 GMT

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,

Possible post some sample data

Regards,

Hirish

Re: Sum based upon Date

Tue, 15 Dec 2015 15:09:44 GMT

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;

Re: Sum based upon Date

Tue, 15 Dec 2015 15:33:58 GMT

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

Re: Sum based upon Date

Tue, 15 Dec 2015 15:41:47 GMT

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.

Re: Sum based upon Date

JonnyPoole — Tue, 15 Dec 2015 16:45:25 GMT

in TMP2, i see this which looks wrong:

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

Date() is a display function. It will store date level granularity and display it according to the 2nd arguement. Date#() is an intepretation function. It will ingest dates that arrive in the format expected by the 2nd argument. It will display with default date formatting.

If you have YYYYMMDD date values at source, you can capture them as dates using date#(DateField,'YYYYMMDD'). If you want to display these dates as YYYYMM you can wrap in the date() function:

Date(Date#(DateField,'YYYYMMDD'),'YYYYMM') ... but i'm not sure that is a good idea because the granularity is still at the day level. You can get duplicate entries for different days in the same month.

To do a month level join , that is why i suggest converting the date to a numerical month value. I like to do that by multipling the YEAR by 100 and adding the month number.

You could do:

Left(DateField,4) * 100 + Mid(DateField,5,2) as YYYYMM

Or you could interpret as dates:

Year( Date#(DateField,'YYYYMMDD') ) * 100 + Month (Date#(DateField,'YYYYMMDD')) as YYYYMM

I like to use the latter in this scenario because it gives me access to the addmonths() function allowing me to do the arithemetic across Years:

Year( addmonths( Date#(DateField,'YYYYMMDD') , -1) ) * 100 + Month ( addmonths( Date#(DateField,'YYYYMMDD') , -1) ) as PriorMonthYYYYMM

With that in mind there are a number of updates to make but generally:

- Use the above technique to form a numerical join based on the month

- probably no reason to have this date#(date() ) structure to a function

- Keep DAY_COD as a date to give date granularity but have Month, Year seperate and YYYYMM as a fourth date bucket for the joins.

Let me know if you need further help.