Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eadbhard
Contributor III
Contributor III

Counting two different expressions by week name

Hi there, I have a list of warehouse reference numbers, each with an arrival date and a departure date:

Dates.png

Although not evident in the table above, some shipments do not have a departure date.

Now I need to create a bar chart, counting how many arrivals and how many departures are processed each week of the year:

chart.png

I have added two columns to the script that loads the shipments,

Load *,
WeekName(Arrival_Date) as Week_Arrival,
WeekName(Departure_Date) as Week_Departure,
Resident Shipments;

And I am using a master calendar script (autogenerated calendar) that has a Week_Name column.

Now for the bar chart, I know the dimension would be the Week_Name column from the calendar, but I don't know what the two expressions for the counting of arrivals and departures per week would be.

Thanks for any help,

-Eduardo

 

2 Replies
sasikanth
Master
Master

Try below option

Assuming that there is no connection with master calendar generated.

Exp1: count( distinct {<Week_Name =p(Week_Arrival)>} shipmentID)

Exp2: count( distinct {<Week_Name =p(Week_Departure)>} shipmentID)

 

Else you need to create one ConicalDate and common calendar for these two dates , check below link

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

 

Thanks,

Sasi

eadbhard
Contributor III
Contributor III
Author

Thanks. When there is a link between the calendar and the shipments table, I get same counting of arrivals and departures in the same week:

11screenshot.jpg

Now, when there isn't a link between the calendar and the shipments, I get the same counting of departures and arrivals for all weeks:

s-shot.jpg

I'll try the canonical date approach and will update.

Thanks!

-Eduardo Alvarez