8 Replies Latest reply on May 16, 2018 11:42 AM by Ronald McPherson

Date Subtraction &

Hello everyone,

I have a data-set with security cameras that were added at various times.  I would like to create a view that looks at what cameras are averaging the most hits on a daily average.

I have a calculated field which gives me a single distinct count of the hits called [# of Hits] - this is a COUNTD(hit_id) calculation

Essentially I'm trying to build a calculated field like this:

([Camera Added Date] - [Current Date, aka TODAY]) / [# of hits]

I feel like this is a relatively simple calculation but I keep hitting walls with the various routes I try.  In advance, thank you for any help that you can provide!

• 1. Re: Date Subtraction &

Do you have a workbook you could attach?

Doing something like this should work:

COUNTD([Hit_ID])

/

TODAY() - [Camera Start Date] + 1

Added one to the denominator as to include the Today in the count.

• 2. Re: Date Subtraction &

May this helps:  ATTR(DATEDIFF('day',  [Camera Start Date], NOW()) )/ SUM([# Hits])

• 3. Re: Date Subtraction &

Thanks, Bryce - gave this a go but getting the 'can't divide integer value by date value error that I've seen in a couple of my previous failed attempts.  I'll try to upload a workbook.

Thanks again.

• 4. Re: Date Subtraction &

Ah, apologies!

Wrapping the denominator in parenthesis should solve that. Simple fix (I think!).

COUNTD([Hit_ID])

/

(TODAY() - [Camera Start Date] + 1)

• 5. Re: Date Subtraction &

Hey Rahul,

It looks like this would work except for the sum function wouldn't give me a distinct sum of the values.

• 6. Re: Date Subtraction &

Thanks, Bryce - appreciate you sticking with me on this one.  Getting a unique error, cannot mix aggregate and non-aggregate arguments with this function.

• 7. Re: Date Subtraction &

Gotcha. That's why Rahul was smart enough to put it wrapped in an ATTR expression above. Heh.

I've wrapped the # of days within MAX() as it'll be unique per camera anyway (assuming you always have the Camera ID or Camera Start Date in the worksheet rows. Otherwise you'll need to create a LOD expression to be safe). I try to use MIN/MAX instead of ATTR where I know I can as it requires less computing than ATTR.

I want to attach...but saw you were on 10.0 and I'm unfortunately on 10.3. Here's an image nevertheless:

Here are the fields I made:

CF_TotalHits:

COUNTD([Hit Id])

TODAY()-DATE([Camera Start Date])

Putting them together:

CF_HitsPerDay:

COUNTD([Hit Id])

/

MAX((TODAY() - [Camera Start Date] + 1))

• 8. Re: Date Subtraction &

Bryce and Rahul - you guys rock, thank you so much!  I had spent hours on this and can't tell you how much I appreciate the help!