Jump to content
Sign in to follow this  
Joyce

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

Share this post


Link to post
Share on other sites
Guest

Hi @Joyce

Try the following (for only yesterdays records):

h_datelogged >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
AND
h_datelogged < curdate()

Hope this helps,

Bob

Share this post


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 

Share this post


Link to post
Share on other sites
Guest

Hi @Joyce

To help me investigate this, could you please take and post a screenshot of the criteria you are using for each for me to compare?

Kind Regards

Bob

Share this post


Link to post
Share on other sites

Hi Bob,

We have decided not to use the yesterday function at the moment. But I will keep it in mind, for next time.

Thanks.

Regards,

Joyce

Share this post


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

 

Share this post


Link to post
Share on other sites
Guest

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

Share this post


Link to post
Share on other sites

HI Bob,

What if the  the SQL Widget  is 'AVG', will the select be h_withinfix or still h_pk_reference?

Regards,

Joyce

Share this post


Link to post
Share on other sites
Guest

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

Share this post


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
Sign in to follow this  

×
×
  • Create New...