Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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.
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.