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.
Re: Counting two expressions by the same dimension
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: