Set analysis syntax

[G3S]

Hi,

Data has: 

Division, OrderNo, TEUperPO, RequiredShipDate, ReqdShipYearWk, ShipmentShippedDate,BookingETSYearWk, Bkg TEU.

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)

 Sum({$<BookedETSYearWk=P(ChartYearWk),ReqdShipYearWk=>}BkgTEU)

2. TEUperPO (sum of TEUperPO where RequiredShipYearWk values are the same as ChartYearWk values and regardless of BookedETSYearWk values)

//current expression used for calculating the sum of TEUperPO: Sum(Aggr(Sum(DISTINCT TEUperPO), OrderNo))

Appreciate help with the correct set analysis syntax for these please.

[sunny_talwar]

Would you be able to share an app where we can see this?

[G3S]

hi @sunny_talwar, 

I've added both charts to show how the selections reflect in each chart. 

Chart X axis based on BookedETSWk.     selection made: BookedETSweek: 2020-33.  

Orders that have different 'RequiredShipWk' (left chart) got shipped in BookedETSWeek 33 (right chart).

Chart X axis based on RequiredShipweek.     selection made: RequiredShipweek: 2020-33.

Orders that  have RequiredShipWk 33 (left chart) got shipped in multiple 'BookedETSWeek' (right chart).

what is required : (please ignore the values shown in below chart, it is not showing correctly).

EstTEU: 1.91k,   Booked TEU: 1.92k

Chart X axis based on ChartWeek.     selection made: ChartWeek: 2020-33.

[sunny_talwar]

@G3S I was hoping for you to share a qvf file so that I can see this first hand. It is difficult to see the whole picture without a sample for something like this.

[G3S]

@sunny_talwar 

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.

DataBridge:

Load
'RequiredShipDate' as Flag_Date,
OrderNo,
RequiredShipDate as ChartDate
Resident OrderHeader;

Load
'BookedETSDate' as Flag_Date,
OrderNo,
BookedETSDate as ChartDate
Resident OrderHeader;

MasterCalendar:

Load

TempDate AS ChartDate,
Year(TempDate)&'-'&week(TempDate) As ChartYearWk
;

TempCalendar:

LOAD

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');