Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How do I show Count of Item against 2 different dates?

Hi,

I've a data set with which excel i've attached.

So I want to be able to display 2 things  Count of ID for both based on Created Date and Closed Date

however seem to only be able to have a single Date dimension.

How would I be able to display the correct ID count based on Close Dates?
Sample.png

 

 

 

 

 

 

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Re: How do I show Count of Item against 2 different dates?

Hello

I do not have sense installation but try to use the following code (change excel path to sense format):

Data:
CrossTable(Type, Date, 1)
LOAD [Problem ID*+] 				as ID, 
     date(floor([Problem Created Date])) 	as Created, 
     date(floor([Closed Date])) 		as Closed
FROM
[CreatedVsClosed.xlsx]
(ooxml, embedded labels, table is [Sheet 1 (2)]);

Then add barchart with 2 dimensions: Date and Type.

QV example in attachment.

View solution in original post

4 Replies
Highlighted
Specialist III
Specialist III

Re: How do I show Count of Item against 2 different dates?

Highlighted
Creator II
Creator II

Re: How do I show Count of Item against 2 different dates?

Hello

I do not have sense installation but try to use the following code (change excel path to sense format):

Data:
CrossTable(Type, Date, 1)
LOAD [Problem ID*+] 				as ID, 
     date(floor([Problem Created Date])) 	as Created, 
     date(floor([Closed Date])) 		as Closed
FROM
[CreatedVsClosed.xlsx]
(ooxml, embedded labels, table is [Sheet 1 (2)]);

Then add barchart with 2 dimensions: Date and Type.

QV example in attachment.

View solution in original post

Highlighted
Contributor III
Contributor III

Re: How do I show Count of Item against 2 different dates?

TempData:
CrossTable(Type, Date, 1)
LOAD [Problem ID*+] 				as ID, 
     date(floor(timestamp#([Problem Created Date],'DD\MM\YYYY hh:mm:ss'))) 	as Created, 
     date(floor(timestamp#([Closed Date],'DD\MM\YYYY hh:mm:ss'))) 		as Closed
Resident Data

 I've changed the script a little to suit my needs by adding timestamp#(Date,'DD\MM\YYYY hh:mm:ss')  to floor and sI'm calling from the resident dataset available in my script that the excel came from.

However when i do check against the Data Model Viewer, notice that my cross table is empty with no data in it not sure whats causing the issue here.

 

Highlighted
Creator II
Creator II

Re: How do I show Count of Item against 2 different dates?

I think the problem is QV auto date recognition algorithm. You use resident table Data. While table loading QV has already tried to recognize date fields using format in TimestampFormat variable.

 

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

After recognition date fields got numeric type. And timestamp#() expects string. When it receives numeric null is returned. Check it.