Setting up h_datelogged measures after a specific date

[dwalby]

Hi all,

I'm trying to create a measure of logged incidents per week, but to only show post go-live Hornbill date in order to prevent the stats from being obscured by the requests we imported from Supportworks. When sampling the query however it is showing weeks prior to the go-live date and also 0s in all samples. Please could someone advise where I'm going wrong? SQL isn't my strong point.

Here is the query where clause I'm using:

(h_requesttype='Incident' OR h_requesttype='Service Request') AND h_datelogged >=  2018-02-11 00:00:00

Our go-live date was 12th Feb

[Guest]

Hi @dwalby

1) The number of samples is defined by the "Maximum Sample History" option on the measure. If you have 52 specified there, the measure will return the last 52 weeks samples, regardless of what you put in your Query where clause.  In this scenario, its going to get the last 52 weeks worth of samples, but I would only expect the most recent one to contain a value higher than 0 (because the most recent sample is the only one later than 11/02/2018. 

The only way to over come this is to start with fewer samples - or at least reduce the Scorecard Limit down to a small number so you only see the recent samples from the past few weeks on your screen. 

2) If your most recent sample is still showing 0 - I think this is because you do not have the date criteria in single quotes. Add them around your date in the Query Where Clause (2018-02-11 00:00:00) - just like you have for the words 'Incident' and 'Service Request' - then save and resample.  The very last sample should contain the count of last weeks calls. 

Hope this helps,

Kind Regards

Bob

[dwalby]

Thanks @Bob Dickinson