Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
My first time posting to this forum but I have been struggling with this for a while - any ideas I'd really appreciate the input!
Essentially I am trying to create an evenly split weekly budget from an existing monthly budget. I have a Master Calendar on the backend in the DLE where I'd like to manipulate the data to actually be able to display this.
As you can see here I have a total budget for January and I'd like to split it between the weeks.
This is my master calendar:
LET vFinCalStart = 7;
"TMP":
Load Distinct Date as [DateTX_TMP]
Resident Sales;
"DATE TMP":
NoConcatenate
load Distinct [DateTX_TMP]
resident [TMP];
drop table [TMP];
CalendarTemp:
Load
Min([DateTX_TMP]) as DateMin,
max([DateTX_TMP]) as DateMax
resident [DATE TMP];
let vMinDate = FieldValue('DateMin',1);
let vMaxDate = FieldValue('DateMax',1);
TRACE $(vMinDate);
Trace $(vMaxDate);
drop table [DATE TMP];
Drop Table CalendarTemp;
"TempCal":
Load Distinct
Date( Date($(vMinDate)) + IterNo()-1 ) AS [DateTX]
AutoGenerate 1
While Date( Date($(vMinDate)) + IterNo()-1 ) <= Date($(vMaxDate));
//Exit Script;
CALENDARTMP:
load distinct
[DateTX] AS TXDate,
DateTX,
Date(DateTX,'YYYY/MM/DD') AS Date,
Num(Year(DateTX) & num(Month(DateTX),'00')) AS Period,
Num(Year(DateTX) & num(Month(DateTX),'00')) AS [#YYYYMM],
num(Month(DateTX),'00') AS [Period No],
Year(DateTX) AS Year,
Year(DateTX) & ' ' & Month(DateTX) AS [Year Month],
MonthName(DateTX) AS [Month Year], //[Month - Year],
// num(month(DateTX)) AS [Month No],
Month(DateTX) AS Month,
num(day([DateTX]),'00')&'-'&weekday([DateTX]-365)&'-'&weekday([DateTX]) AS Day_LY_TY,
num(day([DateTX]),'00') AS [#Day],
'W'&week([DateTX]) As Week,
year([DateTX])&num(week([DateTX]),'00') As Week#,
weekday([DateTX]) AS [Day],
weekday([DateTX]-365) AS [LY Day Name],
if(num(month(DateTX)) < $(vFinCalStart), year(DateTX),year(DateTX)+1) AS [Fin Year],
If(DateTX >= monthstart(addmonths(today(),-12)) and DateTX <= Today(),1) AS [Rolling 12],
If(InMonthToDate( DateTX,today(),0),true(),false()) AS MTD,
If( DateTX - YearStart( DateTX) <= Today() - YearStart(Today()), 1, 0 ) AS [Current Year],
If( DateTX - YearStart( DateTX) <= Today() - YearStart(Today())-1, 1, 0 ) AS [Prior Year],
Mod(num(month(DateTX))- $(vFinCalStart),12)+1 AS [Fin Month No],
num(Mod(num(month(DateTX))-$(vFinCalStart),12)+1,'00') AS [Fin Period No],
MonthStart(date(DateTX,'YYYYMM')) AS RollingPeriod1,
MonthName(DateTX) AS RollingMonth
Resident TempCal
ORDER BY DateTX ASC;
"FIN PERIODS":
left join (CALENDARTMP)
load * INLINE [
Month No,Fin Month,Cal Sort
1, Jul, 7
2, Aug, 8
3, Sep, 9
4, Oct, 10
5, Nov, 11
6, Dec, 12
7, Jan, 1
8, Feb, 2
9, Mar, 3
10, Apr, 4
11, May, 5
12, Jun, 6
];
DROP Table TempCal;
"Calendar":
NoConcatenate
Load *,
// [Fin Year] & if([Fin Month No] < 10,'0')& [Fin Month No] AS [Fin Period],
// [Fin Year] & ' ' & [Month] AS [Fin Year Month],
// [Month] & ' ' & [Fin Year] AS [Fin Month Year]
IF([Month No] = 1 OR [Month No] = 2 OR [Month No] = 3,'Q1',
IF([Month No] = 4 OR [Month No] = 5 OR [Month No] = 6,'Q2',
IF([Month No] = 7 OR [Month No] = 8 OR [Month No] = 9,'Q3',
IF([Month No] = 10 OR [Month No] = 11 OR [Month No] = 12,'Q4')))) AS [Quarter],
IF([Fin Month No] = 1 OR [Fin Month No] = 2 OR [Fin Month No] = 3,'Q1',
IF([Fin Month No] = 4 OR [Fin Month No] = 5 OR [Fin Month No] = 6,'Q2',
IF([Fin Month No] = 7 OR [Fin Month No] = 8 OR [Fin Month No] = 9,'Q3',
IF([Fin Month No] = 10 OR [Fin Month No] = 11 OR [Fin Month No] = 12,'Q4')))) AS [Fin Quarter]
Resident "CALENDARTMP"
ORDER BY DateTX ASC;
drop table "CALENDARTMP";
"TMP_YYYYMM":
Load
fieldvalue('Period',rowno()) as [#YYYYMM_TMP]
AutoGenerate(fieldvaluecount('Period'));
"TMP_YYYYMM_SEQ":
noconcatenate
load distinct
[#YYYYMM_TMP] as [#YYYYMM]
resident [TMP_YYYYMM]
order by [#YYYYMM_TMP];
left join (Calendar)
load distinct
[#YYYYMM] as [#YYYYMM],
rowno() as [#YYYYMM_Seq]
resident [TMP_YYYYMM_SEQ];
drop table [TMP_YYYYMM_SEQ];
drop table TMP_YYYYMM;
"TMP_WEEK#":
Load
fieldvalue('Week#',rowno()) as [Week#_TMP]
AutoGenerate(fieldvaluecount('Week#'));
"TMP_WEEK_SEQ":
noconcatenate
load distinct
[Week#_TMP] as [Week#]
resident [TMP_WEEK#]
order by [Week#_TMP];
left join (Calendar)
load distinct
[Week#] as [Week#],
rowno() as [Week#_Seq]
resident [TMP_WEEK_SEQ];
drop table [TMP_WEEK#];
drop table "TMP_WEEK_SEQ";
