Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Can you explain with sample data?
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 |
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
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
thanks at all for your great help. Many thanks again.