Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
G3S
Contributor III
Contributor III

Set analysis syntax

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.

Tables.JPG

 

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.

4 Replies
sunny_talwar

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

G3S
Contributor III
Contributor III
Author

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).

ETS.JPG

 

 

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).

ReqShip.JPG

 

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.

chart week.JPG

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
Contributor III
Contributor III
Author

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

 

 

Capture.JPG