Hi all,
I'd be very grateful for any help:
I currently have monthly budget data. I have created a temp calendar to introduce more granularity, i.e. the number of weeks in each month so I may split out the monthly budget and divide it by the number of weeks in each month.
It works for January, counting six weeks. However for February, it's counting 5 weeks and I'd only expect 4, running Monday to Sunday February 2021. This is happening in other months as well, effectively double counting weeks at month beginning/end. This has the effect of giving me 63 weeks for the year. See the attached sheet.
Sheet "Raw Data Input" is the budget table I'm loading as the input
Sheet "Temp Calendar example" is the output from the calendar
Sheet "2021 Week dates" is what I'm trying to achieve with the additional column detailing the number of weeks in the month (in the Temp Calendar Example" sheet as "nbweek"
I've left in some of my attempts to fix the issue with //
Full Script:
Unqualify *;
[Sheet1]:
LOAD
[Budget Category],
Date([Month] ,'DD/MM/YYYY') AS [CDate],
MonthName([Month]) As [Month Name],
Floor(week(MonthEnd(Month)))-Floor((week(MonthStart(Month)))-1) AS [Weeks in Month], //This one double counts weeks in month
//Floor((monthend(Month)-MonthStart(Month))+1) As [Weeks in Month2],
//Count(DISTINCT Week($(varcMinDate) + RecNo()) - 1) AS [Weeks in Month2],
[Budget amount],
[Budget amount]/week(MonthEnd(Month))-(week(MonthStart(Month))-1) As [Weekly Budget],
[Budget amount]/round(YearEnd(Today())-YearStart(Today())) As [Daily Budget],
If([Budget Category]= 'Respite Care' or [Budget Category]= 'Equipment Top-Up Fund - purchase' or [Budget Category]= 'Equipment Top-Up Fund - rental' or [Budget Category]= 'Adaptations to Property' or [Budget Category]= 'Environmental Controls', 'Care Grant',
If([Budget Category]= 'Quality of Life','Quality of Life',
If([Budget Category]= 'Young Persons Grant','Young Persons Grant',
If([Budget Category]= 'Wheelchairs','Wheelchairs',
If([Budget Category]= 'Communication Aids','Communication Aids',
'Carers Grant'))))) AS [Budget Re-categorised]
FROM [lib://AttachedFiles/Care Support Grant Budget 2021 V4.xlsx]
(ooxml, embedded labels, table is Sheet1);
cMax_Date:
LOAD
Max([CDate])+30 as cmax_date
Resident Sheet1;
cMin_Date:
LOAD
Min([CDate]) as cmin_date
Resident Sheet1;
let varcMinDate = Peek('cmin_date',0,'cMin_Date')*1;
let varcMaxDate = Peek('cmax_date',0,'cMax_Date')*1;
//DROP Tables cMax_Date,cMin_Date;
/*---------------------------Temp Calendar---------------------------*/
CTempCalendar:
LOAD
//$(varcMinDate) + RecNo()-1 as cNum,
MonthStart($(varcMinDate) + RecNo() - 1) as CDate,
Week($(varcMinDate) + RecNo() - 1) as Week,
//Floor((monthend(Month)-MonthStart(Month))-1) As [Weeks in Month2],
MonthName($(varcMinDate) + RecNo() - 1) as Month,
Week(today()) as [Current week],
round(YearEnd(Today())-YearStart(Today())) as [Number of Days]
//Day([Month]($(vmindate)+RowNo()-1)) as NumberOfDays,
AutoGenerate $(varcMaxDate) - $(varcMinDate) +1;
Join(CTempCalendar)
LOAD Distinct
CDate,
Count(DISTINCT Week) as nbWeek,
Month
//AutoNumber(Week([Month]),MonthName(Month)) as [Month Week2]
Resident CTempCalendar
Group By CDate, Month;
// //[Daily Budget]:
// LOAD *,
// [Budget amount]/[Number of Days] AS [Daily Budget],
// [Budget amount]/[Weeks in Month] AS [Weekly Budget]
// RESIDENT [Sheet1_temp_7f5343a2-0b30-a8e5-16f0-41b11049];
// DROP TABLE [Sheet1_temp_7f5343a2-0b30-a8e5-16f0-41b11049];
// [Daily Budget]:
// LOAD *,
// //[Budget amount]/round(YearEnd(Today())-YearStart(Today())) AS [Daily Budget],
// [Budget amount]/Count(DISTINCT Week($(varcMinDate) + RecNo() - 1) ) AS [Weekly Budget]
// RESIDENT [Sheet1];
// DROP TABLE [Sheet1];
//RENAME TABLE [Sheet1_temp_7f5343a2-0b30-a8e5-16f0-41b11049] to [Sheet1];
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [Month] USING [autoCalendar] ;
