topic Re: Counting two different expressions by week name in QlikView

Counting two different expressions by week name

eadbhard — Wed, 25 Nov 2020 16:16:04 GMT

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

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:

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

Re: Counting two different expressions by week name

sasikanth — Tue, 25 Jun 2019 07:08:05 GMT

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

Re: Counting two different expressions by week name

eadbhard — Tue, 25 Jun 2019 17:21:58 GMT

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:

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:

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

Thanks!

-Eduardo Alvarez