Set Analysis Count values over 20 days

[#1]

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

[sunny_talwar · Accepted solution]

May be this:

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

[maxgro]

=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)

[Anonymous]

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'

[sunny_talwar]

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

[jonathandienst]

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.