Count qty of dates and assign the qty to a timeline

[michael_s]

Hi all,

I want to create a bar chart which shows the qty of inquiries and orders per month as grouped bars. The provided data has the following structure (date: YYYY/MM):

OrderID	Inquiry Target Date	Order Target Date
12345/784	2022/01	2022/03
12345/785	2022/02	2022/03
12345/786	2022/03	2022/05
12345/787	2022/04	2022/05
12345/788	2022/06	2022/06
12345/789	2022/07	2022/09

To illustrate the expected outcome I've added the table below. 

Date (Dimension)	Qty Inquiries (Measure)	Qty Orders (Measure)
2022/01	1	0
2022/02	1	0
2022/03	1	2
2022/04	1	0
2022/05	0	2
2022/06	1	1
2022/07	1	0
2022/08	0	0
2022/09	0	1

I would prefer to also keep empty months (without any inquiry/order).

To show only one bar was no problem, but I can't figure out what to do in order to get the correct second column (bar).

So far I always got the same amount for column Order as for column Inquiry.

Thanks for your help!

Michael

[maxgro · Accepted solution]

You can try in Qlik Sense with this script

S:
LOAD
OrderID,
"Inquiry Target Date",
"Order Target Date"
FROM [lib://WebCommunity]
(html, utf8, embedded labels, table is @1);

S2:
LOAD
OrderID,
[Inquiry Target Date] AS Date,
'I' as Type
RESIDENT S;

CONCATENATE(S2)
LOAD
OrderID,
[Order Target Date] AS Date,
'O' as Type
RESIDENT S;

WebCommunity 

https://community.qlik.com/t5/New-to-Qlik-Sense/Count-qty-of-dates-and-assign-the-qty-to-a-timeline/...

and this chart

Here you can find an useful document

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

[michael_s]

Thanks a lot! This works great!

Also thanks for the link - very interesting. Unfortunately it didn't show up during my search. Never read or heard of canonical before, though - even in my mother tongue.

(I already had to think for ages to find a somewhat suitable subject)