.gif)
Simple but powerful expressions to quickly filter data. I use them often by storing these values on a 'DateRangeExpression' Property Control and passing the expression directly to the 'Data Limited by Expression' option: ${DateRangeExpression}
-
| Today | [Date]=Date(DateTimeNow()) |
| Last 30 Days | [Date] >= dateadd('dd',-30,DateTimeNow()) |
| This Week | Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow()) |
| This Month | Month([Date]) = month(DateTimeNow()) and Year([Date]) = year(DateTimeNow()) |
| Year to Date | Year([Date]) = Year(DateTimeNow()) |
| Last Month Last 12 Months | Month([Date]) = Month(dateadd('mm',-1,DateTimeNow())) and Year([Date]) = Year(dateadd('mm',-1,DateTimeNow())) [Date] >= dateadd('mm',-12,DateTimeNow()) |
| Q1 | Quarter([Date]) = 1 and Year([Date]) = Year(DateTimeNow()) |
| Q2 | Quarter([Date]) = 2 and Year([Date]) = Year(DateTimeNow()) |
| Q3 | Quarter([Date]) = 3 and Year([Date]) = Year(DateTimeNow()) |
| Q4 | Quarter([Date]) = 4 and Year([Date]) = Year(DateTimeNow()) |
| Begining of Month | Date(Year(DateTimeNow()),Month(DateTimeNow()),1) |
| End of Month | DateAdd('day',-1,date(Year(DateTimeNow()),Month(DateAdd('month',1,DateTimeNow())),1)) |
| End of Last Month | DateAdd("day",-1,date(Year(DateTimeNow()),Month(DateTimeNow()),1)) |
| End of Next Month | DateAdd("day",-1,date(Year(DateTimeNow()),Month(DateAdd("month",2,DateTimeNow())),1)) |
| First Weekending of the year | Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)), DATE(year(DateTimeNow()),1,1)) |
| Last Weekending (last saturday) | DateAdd("week",Week(DateTimeNow()) - 2,Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1))) |
| End of this week | DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1))) |
| End of next week | Date(DateAdd("week",Week(DateTimeNow()),Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))) |
| Previous Quarter end date | DateAdd("day",-1,DateAdd("quarter",Quarter(DateTimeNow()) - 1,Date(Year(DateTimeNow()),1,1))) |
| - | - |
4 comments:
Great help. Thanks!
I'm trying to apply the functionality, but after creating in "Document Properties -> Properties (after New), I put the function of what type? I've tried date, string, integer, but when I apply the function in" Data Limited Expression ", that this the Properties is "Boolean Type", how do you make this?
If possible, can you forward the step by step?
Regards,
Vanice
Hello Vanice,
You can use these expressions directly on the 'Data Limited by' in your visualization properties > Data > Data Limited by and type the expression. For exampl,e if you want to see only the last 30 days, just put:
[yourDatecoumn] >= dateadd('dd',-30,DateTimeNow())
Post a Comment