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.
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:
.png "Former Employee"){kind=icon}