topic Re: Manage dates in QlikView

Manage dates

sylvain_piccarreta — Tue, 28 Sep 2021 08:17:45 GMT

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

Re: Manage dates

PrashantSangle — Tue, 28 Sep 2021 09:12:34 GMT

Can you explain with sample data?

Re: Manage dates

sylvain_piccarreta — Tue, 28 Sep 2021 12:33:38 GMT

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

Re: Manage dates

PrashantSangle — Tue, 28 Sep 2021 12:50:59 GMT

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

Re: Manage dates

MarcoWedel — Tue, 28 Sep 2021 19:58:05 GMT

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

Re: Manage dates

sylvain_piccarreta — Thu, 30 Sep 2021 16:18:23 GMT

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