5 Replies Latest reply: Jan 19, 2016 6:23 AM by Jonathan Dienst

# Set Analysis Count values over 20 days

Hi,

I'm hoping somebody can assist with the following expression:

=count({\$<Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'}, Date={'=date(today()-20,'dd/mm/yyyy''}>})distinct(ID))

I'm trying to count the number of unique IDs where the latest date is over 20 days, how can I do this? the above formula that I tried doesn't work.

Best

Mav

• ###### Re: Set Analysis Count values over 20 days

May be this:

=Count({\$<Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'}, Date={"\$(='<=' & Date(Today() - 20, 'DD/MM/YYYY'))''}>} Distinct ID)

• ###### Re: Set Analysis Count values over 20 days

=sum({<

Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={"\$(=date(today()-20, 'DD/MM/YYYY'))"}

>} distinct ID)

or

=sum({<

Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={"<=\$(=date(today()-20, 'DD/MM/YYYY'))"}

>} distinct ID)

• ###### Re: Set Analysis Count values over 20 days

may be this, some syntax missing?

=count({<Date=, Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={'=\$(=date(today()-20,'dd/mm/yyyy'))'} >} distinct(ID))

Make sure format of Date should be 'dd/mm/yyyy'

• ###### Re: Set Analysis Count values over 20 days

mm is for minutes Balraj. You will need to use MM here

• ###### Re: Set Analysis Count values over 20 days

Looks OK to me,except that the date format is incorrect

date(today()-20,'dd/mm/yyyy''}

mm is minutes, you need MM in months

date(today()-20,'dd/MM/yyyy''}

I suggest that you test these sub expressions in a text box; That will help you debug the expression.