Calculated Field - Count, Datepart

[LWSChad]

Hello All,

I would like to COUNT the values between a certain time frame regardless of date... for this example lets say 10AM and 4PM or 10:00 and 16:00.

 

Please consider:

Item1: 01/07/15 11:00:00

Item2: 01/04/15 13:00:00

Item3: 01/03/15 07:00:00

Item4: 01/06/15 17:00:00

Item5: 01/03/15 09:00:00

Item6: 01/07/15 15:00:00

 

Desired results: 3

 

 

 

I can pull the hour from the date, but I can't figure out how to filter by hour or count values in a range.

 

Any ideas?

 

Thanks

 

[iren]

Hi Chad,

 

You can use following code in aggregation field: 

COUNT(CASE WHEN (DATEPART(hh, DateTime) >= 10 AND DATEPART(hh, DateTime) <= 16) THEN DateTime ELSE NULL END)

DateTime should be changed on your value.

 

Hope it helps.

[LWSChad]

Awe

 

 

Hi Chad,

 

You can use following code in aggregation field: 

COUNT(CASE WHEN (DATEPART(hh, DateTime) >= 10 AND DATEPART(hh, DateTime) <= 16) THEN DateTime ELSE NULL END)

DateTime should be changed on your value.

 

Hope it helps.

 

Awesome!!

[Aether]

You can also try to check this helpful documentation about dates and calculations:

https://howto.caspio.com/faq/reports-datapages/calculated-fields-and-datediff-function/
https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

https://howto.caspio.com/function-reference/