// we create a calender that gives a date range from today to 12 months from today
Let vMinDate = num(MonthStart(Today())) ; Let vMaxDate =Num(AddMonths(Today(),12)); Let vCurrentYear = Year(Today());
Temp: Load // load calender $(vMinDate) + (IterNo()) as DateNum // set Iterno() AutoGenerate 1 While $(vMinDate) + (IterNo()) <= $(vMaxDate) ; masterCalendar: Load DateNum as MasterDate, Num(Year(DateNum),'0000') & Num(Month(DateNum),'00') as DateKey, // Date(Date,'WWWW') as Day, Date(DateNum,'MMMM') as Month, Month(DateNum) as MonthAbbr, num(Month(DateNum)) as MonthNum, Year(DateNum) as Year, Year(DateNum) &'-'& Month(DateNum) as YearMonthCalender, 'Q' & ceil(Month(DateNum) / 3) as Quarter, 'Q' & ceil(Month(DateNum) / 3) &'-'& Year(DateNum) as [Quarter Year] Resident Temp; Drop Table Temp;
// Define Variable to show 'Month-CurrentYear CQ' starting with January Let vCommitMonths = '['&Peek('Month',0,'masterCalendar')&'-'& $(vCurrentYear)&' CQ]';
Load Text("Part Number") As [Part Number] ,Text("Ordering Plant") As [Deliver-To Plant] ,Text("Supply Plant") As [Supply Plant] ,Text("Source Code") As [Source Code] ,Sum($(vCommitMonths)) As [Current Commit] From XYZ
Group By "Part Number", "Ordering Plant", "Supply Plant", "Source Code";
but i keep getting this error The following error occurred: Field 'May-2023 CQ' not found
The error occurred here: Load Text("Part Number") As [Part Number] ,Text("Ordering Plant") As [Deliver-To Plant] ,Text("Supply Plant") As [Supply Plant] ,Text("Source Code") As [Source Code] ,Sum([May-2023 CQ]) As [Current Commit] From XYZ
Group By "Part Number", "Ordering Plant", "Supply Plant", "Source Code"
which i know for sure that field is availble in the Source Data. what am i doing wrong any help would be much appreciated.
you are using from in script - Meaning your data is coming from a database table. Also in the load of from you are using :- Sum($(vCommitMonths)) As [Current Commit] Here vCommitMonths is set as :- '['&Peek('Month',0,'masterCalendar')&'-'& $(vCurrentYear)&' CQ]' and its first value comes out to be: - [May-2023 CQ] but there is no field named [May-2023 CQ] in the table and that's why it is showing an error
