Jump to content

Function for yesterday's date


Recommended Posts

Hi 

What function can be used in reporting to get yesterday's date in Horn bill?

I have tried: h_datelogged >= CURRENT_DATE- 1 ,and it doesn't give me correct value

Thanks.

Regards,

Joyce

Link to post
Share on other sites

Thanks Bob.

I have try the above, but when compare a count from 'Daily measure' and the count from above, I am not getting the same value.

Do i need to specify times?

Thanks.

Regards,

Joyce

 

Tha 

Link to post
Share on other sites

Hi @Bob Dickinson

 

I am getting similar problem with a month count compare to measure.

This is a count from counter widget gives me a 714:

image.thumb.png.b6389999a9775a02d53543cdc37b3deb.png

 

While the 'Monthly Measure' for the same count gives me 728

image.thumb.png.b0cf12aebfa403e8d920d84ffbc1abe1.pngimage.thumb.png.e04cf53570bb15d3294952012e225d13.png

Am I doing the dates wrong?

Thanks.

Regards,

Joyce

 

Link to post
Share on other sites

Hi @Joyce

Both need a little bit of amendment:

1) In your SQL Widget, you need to change the h_withinfix at the top next to COUNT, to h_pk_reference

2) Also in your SQL Widget, you need to now add in the h_withinfix to the WHERE statement so simply add:
AND h_withinfix = 1

This should provide hopefully the true count

In your measure:

3) Your title states measuring a percentage here, but you are actually measuring a count, so you may want to amend this

4) As above, you need to add the h_withinfix to the WHERE statement so simply add:
AND h_withinfix = 1

Now if you resample, do the values match?

Kind Regards

Bob

Link to post
Share on other sites

Hi @Joyce

The average is slightly different as its a mathematical function based on the values that are returned from the column specified, divided by the count. 

E.g lets take your query above - if you did the average h_withinfix, and there where 600 results - it would add the h_withinfix values together of all of them, and divide by 600. Because the h_withinfix contains a 1 or a 0, and you have already filtered where h_withinfix = 1, the results would be:

1 + 1 + 1 + 1 (etc 600 samples) = 600 
Divided by 600 
= 1 (Thats the average value)

So as you can see it doesn't really give us any decent information 

The average wouldn't work if you entered h_pk_reference as they  arn't integers (i.e. you can't have SR0000182 + IN00000827 + IN00000987 etc) 

Where this works better is when you have a number that is variable per incident. For example, number of times request reopened. If you had 600 samples, the calculation would be something like:

0 + 0 + 1 + 2 + 0 + 1+ 0 + 0 + 0 + 0 + 0 ( etc 600 samples) = 44
Divided by 600
= 0.07 Average number of reopened calls

Kind Regards

Bob

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...