Counting two expressions by the same dimension

[eadbhard]

Hi there,

I have a fact table with shipment number, shipment arrival week and shipment departure week columns. Fact table is linked to a master calendar table that has a calendar week column:

I need a bar chart where the dimension would a week, and the expressions would be two bars, one showing how many shipments arrived in the week and another bar showing how many shipments departed in the same week. Something like this :

Now, I think the dimension of the chart would be the calendar week from the calendar table, but I can't figure out the suitable "Count(shipment_number)" expressions.

Any help is greatly appreciated, thank you!

[dplr-rn · Accepted solution]

i think you need canonical date. see below link

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

[eadbhard]

Thank you, you got that right! I made an additional, 'canonical date' based table, where each record has only three columns: the shipment number, a date (the canonical date), and the "Event" column, which could be either 'Received' or 'Shipped'. Then the expressions were relatively easy to figure out: 

=Count({<Event={'Received'}>} Shipment_Number)
=Count({<Event={'Shipped'}>} Shipment_Number)

The dimension was the canonical date on the master calendar, which is linked to the canonical date of the additional table I created.

Thanks a lot!

-Eduardo

[dplr-rn]

my pleasure