topic Counting two expressions by the same dimension in QlikView

Counting two expressions by the same dimension

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

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!

Re: Counting two expressions by the same dimension

dplr-rn — Thu, 25 Jul 2019 21:30:40 GMT

i think you need canonical date. see below link

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

Re: Counting two expressions by the same dimension

eadbhard — Fri, 26 Jul 2019 15:27:06 GMT

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

Re: Counting two expressions by the same dimension

dplr-rn — Fri, 26 Jul 2019 15:28:38 GMT

my pleasure