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!

[#2]

No, it is a count of 0

[pradosh_thakur]

hi stalwar1‌

can you please point out the error in my expression ?

regards

Pradosh

[Anil_Babu_Samineni]

There is small mistake, That may be the cause. Can you trouble shoot to use " " instead of [ ]

[#5]

[#6]

Thank you everyone.

The correct answer I found is

Sum (if( Aggr( [EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]  )  >= 6 , 1, 0 ))

Which is borrowed from a previous question's answer. I am now having trouble adapting this for the values that fall between numbers. So now, I need a count of those between 0 and 3. Can't get the formatting right.

[sunny_talwar · Accepted solution]

Just this

Sum(If(

Aggr([EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]) >= 0 and

Aggr([EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]) <= 3, 1, 0))

[#8]

You rock! Thank you.