Set analysis Date Range

[#1]

Hello Community,

I have a problem with set analysis.  What i wnat to do is count a certain ID, for the last fives year,

here is my expression

My solution : Count({$<Date={'>=(Max(Year(Date)))<=(Max(Year(Date)))-5>} DISTINCT([ID SIG])

But this ins't work !

If someone have the solution, i'll apreciate it. Thanks.

[sunny_talwar]

This may be:

Count({$<Date={"$(='>=' & Date(AddYears(Max(Date), -5), 'YourDateFormat') & '<=' & Date(Max(Date), 'YourDateFormat'))>} DISTINCT [ID SIG])

[#3]

Hello Sunny,

Pour info, [Date] is my date field (is a numeric date format), so what i have to do with 'AddYears' and 'YourDateFormat' ?

Thnks you

[maxgro]

=count({<Date={">=$(=Date(AddYears((Max(Date)),-5)))<=$(=Date(Max(Date)))"}>} distinct [ID SIG]) 

or

=count({<Date={">=$(=Num(AddYears((Max(Date)),-5)))<=$(=Num(Max(Date)))"}>} distinct [ID SIG]) 

[sunny_talwar]

You won't need to worry about AddYears. For YourDateFormat -> You need to make sure that you input the format Date Field is in within the Single Quotes. For instance if Date is in this format MM/DD/YYYY then use this within single quotes.

HTH

Best,

sUNNY

[sunny_talwar]

Sorry missed a double quote at the end:

Count({$<Date={"$(='>=' & Date(AddYears(Max(Date), -5), 'YourDateFormat') & '<=' & Date(Max(Date), 'YourDateFormat'))">} DISTINCT [ID SIG])

[jagan · Accepted solution]

Hi,

If date is numeric format then try below expression

=count({<Date={">=$(=Num(AddYears((Max(Date)),-5)))<=$(=Num(Max(Date)))"}>} distinct [ID SIG])

Regards,

Jagan.

[#8]

Thanks you guys, it works great !!!