Need to show in a chart sum of TEUper PO and sum of BkgTEU by week.
if plotting chart based on either ShipmentShippedDate or RequiredShipDate, the summary is not right since an OrderNo can have these two dates in different weeks. depending on the date chosen for chart, the details would be like the cells marked in grey, instead of the yellow and green. (if first table as example, it will show for wk 33: BkgTEU 1616 and TEUperPO 724-instead of 1862)
But if Master Calendar is used, this issue can be overcome. bottom table below with one row of data.
Master calendar is not based on any dimension in the data. It is calculated based on today(). as ChartDate.
Chart based on Week(ChartDate), tried the following syntax, but not getting the right answer. if two weeks are selected, their combined value is being returned for each week.
1. BkgTEU (sum of Bkg TEU where BookedETSYearWk values are the same as ChartYearWk values and regardless of RequiredShipYearWk values)
I added the following to the script and made the chart like further below. but the values in chart (title: ChartWeek) are not correct + when checking the ChartWeek has null values showing as - (see table in screenshot). I'm attaching the dataset.
Load 'RequiredShipDate' as Flag_Date, OrderNo, RequiredShipDate as ChartDate Resident OrderHeader;
Load 'BookedETSDate' as Flag_Date, OrderNo, BookedETSDate as ChartDate Resident OrderHeader;
TempDate AS ChartDate, Year(TempDate)&'-'&week(TempDate) As ChartYearWk ;
MinDate + Iterno()-1 As TempDate While MinDate + Iterno()-1 <=MaxDate ;
Load Min(FieldValue('ChartDate',RecNo()))-1 as MinDate, Max(FieldValue('ChartDate',RecNo())) as MaxDate AutoGenerate FieldValueCount('ChartDate');