Count Function

[#1]

Hi! Wondering how this is supposed to be formatted.

Count(distinct{$<[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] = {">3"}>} [ASSET TITLE])

This function doesn't work. Basically, I want to count the number of asset titles in which the Date modified plus the number of months in the expiration field minus today is greater than 3 months.

An asset's date modified is the last time it was touched. It's expiration is the amount of months it takes past when it was last touched for it to expire, which we can assume is 24 months right now. The last date modified field is today. I want to say, if an asset has been touched for 24 months (in this situation but the number will be different by each asset), count it. So, if the date it was touched + expiration (which gives you the date it will expire) is more than 3 months away from today, count it.

Please help!

[sunny_talwar]

May be this

Count(DISTINCT{$<[ASSET TITLE] = {"=[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] > 3"}>} [ASSET TITLE])

[Anil_Babu_Samineni]

One simple solution would be create field in script

Load DATE MODIFIED, EXPIRATION, DATE MODIFIED.autoCalendar.Date,

([DATE MODIFIED] + EXPIRATION) - [DATE MODIFIED.autoCalendar.Date] as Field3Combined;

Then

Count(distinct {< Field3Combined = {">3"}>} [ASSET TITLE])

[sunny_talwar]

Or for three months, you might need this (assuming 90 days in 3 months)

Count(DISTINCT{$<[ASSET TITLE] = {"=[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] > 90"}>} [ASSET TITLE])

[pradosh_thakur]

Try this

please try altering syntax a bit if it fails

Count(DISTINCT{$<[ASSET TITLE] =

{"$(DATE(AddMonths([DATE MODIFIED],EXPIRATION),'DD/MM/YYYY') - date(today(),'DD/MM/YYYY')) > 90"}

>} [ASSET TITLE])

regards

Pradosh

[#6]

I would like to do this. However, when I put it in the script, it has an error and says that the field "Date Modified" is not found?

I changed the function as well but not the formatting.

[#7]

This doesn't recognize anything after " as a field?

[sunny_talwar]

Don't like my ideas?

[pradosh_thakur]

you didn't get any result?

[#10]

You have helped me more than anyone! I have been trying out yours. I changed the function a bit so that its simply

Expiration - Date Modified Months Ago which gives me the result i need. So then I need a count of the asset titles in which Expiration - Months ago >3