- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set analysis Date Range
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This may be:
Count({$<Date={"$(='>=' & Date(AddYears(Max(Date), -5), 'YourDateFormat') & '<=' & Date(Max(Date), 'YourDateFormat'))>} DISTINCT [ID SIG])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry missed a double quote at the end:
Count({$<Date={"$(='>=' & Date(AddYears(Max(Date), -5), 'YourDateFormat') & '<=' & Date(Max(Date), 'YourDateFormat'))">} DISTINCT [ID SIG])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks you guys, it works great !!!