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

[Keitaru]

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?

[agni_gold]

https://community.qlik.com/t5/QlikView-Documents/Interval-Match-Feature-Function/ta-p/1477919

This might help you

[mrybalko · Accepted solution]

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.

[Keitaru]

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.

[mrybalko]

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.