Function for yesterday's date

[Joyce]

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

[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

[Joyce]

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 

[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

[Joyce]

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

[Joyce]

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:

 

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

Am I doing the dates wrong?

Thanks.

Regards,

Joyce

 

[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

[Joyce]

HI Bob,

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

Regards,

Joyce

[Joyce]

They provide different answers

 

[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