Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
ailliano
New Contributor

Showing Data from last 24hrs

I have data in a table that records how long it takes to run a report and if the report completed or failed.  It basically has report name, Status, start time and end time.  The Start Time is formatted as such:

 

START_TIME
29-AUG-18 05.12.41.382000000 PM

I just want to grab a count of reports that ran in the past 24hrs and their status.  Trying to show something like this:

StatusCount
COMPLETED120
FAILED5
RUNNING30

          

How can I do that in qlikview?

I tried: =Count({<STATUS>,START_TIME >= {'>=$(=timestamp(now()-1))'}})

But I get no count numbers.  Any advice?

6 Replies
MVP & Luminary
MVP & Luminary

Re: Showing Data from last 24hrs

There are a few syntax-issues and therefore try it with this:

= Count({<STATUS =, START_TIME = {">=$(=now()-1)"}>} [report name])

Important is further that your START_TIME is really a timestamp.

- Marcus

Highlighted
Partner
Partner

Re: Showing Data from last 24hrs

As like above try below..

= Count({<

STATUS ={'*'}

, Date_field = {">=$(=today()-1)"}

>}ID)

Note: Make sure the date field format and validate with today or Now functions in the text box.

ailliano
New Contributor

Re: Showing Data from last 24hrs

I am loading data from an Excel file.  Is there a way I can make sure the data in the START_TIME column is being loaded as a DATE format into QLIK when using the Data Load Editor?

Example:

LOAD

ReportName,

Status,

Start_Time,

End_Time

FROM [lib://qlik_RptData.xls (ad_JDoe)/qlik_RptData.xls]

(biff, embedded labels, table is [Export Worksheet$]);

Can I CAST/CONVERT that Start_Time into a DATE FORMAT (in case it's being read as text)?

If so, what would be that syntax considering the data is formatted as: 29-AUG-18 05.12.41.382000000 PM?


Thanks!

MVP & Luminary
MVP & Luminary

Re: Showing Data from last 24hrs

If it's really a string - you could just check it with num(Start_Time) which will return either NULL by a string or a float by a numeric content - you could convert and format it with something like:

timestamp(timestamp#('29-AUG-18 05.12.41.382000000 PM', 'DD-MMM-YY hh.mm.ss.fff TT'))


- Marcus

ailliano
New Contributor

Re: Showing Data from last 24hrs

Unfortunately none of these suggestions worked...

I changed my connection to now connect directly to Prod DB (instead of reading from Excel file)

To make this simpler, how can I just get a count of the various statuses?  I don't care if it's last 24 hrs. I just want a total count of the diff statuses.

COMPLETED: 320

FAILED:12

RUNNING: 56

etc..

MVP & Luminary
MVP & Luminary

Re: Showing Data from last 24hrs

Regardless from the used source you need to know exactly how the data are stored/transferred which might be different to how they are displayed. If your applied formatstring differs in any way from the real fieldvalues a conversion will fail - I think you should give it a few more (systematically) trials because you will need these measures again and again.

Get the Dates Right

Why don’t my dates work?

On Format Codes for Numbers and Dates

If your dates and times work you could just use Status as dimension in an object to get with = Count([report name]) your results. Without such a dimension you could put a Status query into the expression with something like: = Count({<STATUS = {'COMPLETED'} >} [report name]).

- Marcus