Manage dates

[sylvain_piccarreta]

Hi,

I have a question to manage 2 dates. I want to count the number of command by 2 dates (created and closed).

I would like to have in the same graphics (or table) the number of commands by month created and the number of command closed.

Do you have some idea to do it please ?

Thanks for your help,

Sylvain

[PrashantSangle]

Can you explain with sample data?

[sylvain_piccarreta]

Of course.

I have a table with 

# Command	created date	closed date
1	01/02/2021
2	14/02/2021	01/02/2021
3	04/03/2021	17/04/2021
4	14/03/2021	15/04/2021
5	01/04/2021	05/05/2021

In the graphics/tble, I search to have the result

Period	#created command	# closed command
02-2021	2	1
03-2021	2
04-2021	1	2
05-2021		1

[PrashantSangle]

Hi,

try below 

in script

data:
Load Command,
Date(Date#(created_date,'DD/MM/YYYY')) as created_date,
Date(Date#(closed_date,'DD/MM/YYYY')) as closed_date
Inline [
Command, created_date, closed_date
1, 01/02/2021,
2, 14/02/2021, 01/02/2021
3, 04/03/2021, 17/04/2021
4, 14/03/2021, 15/04/2021
5, 01/04/2021, 05/05/2021
];

final:
Load Command as cmd,
'create' as flag,
Date(Monthstart(created_date),'MM.YYYY') as Period
Resident data;

Concatenate

Load Command as cmd,
'closed' as flag,
Date(Monthstart(closed_date),'MM.YYYY') as Period
Resident data;

in front end

table

dimension > Period

Measure

1: #created command > count({<flag={"create"}>}Distinct cmd)

2: # closed command > count({<flag={"closed"}>}Distinct cmd)

Regards,

Prashant Sangle

[MarcoWedel · Accepted solution]

Hi,

another solution might be a crosstable load:

table1:
CrossTable (CreatedClosed, Date)
LOAD [# Command], 
     [created date], 
     [closed date]
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Manage-dates/m-p/1839616] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[sylvain_piccarreta]

thanks at all for your great help. Many thanks again.