Create Custom Date Items to be used for Display or Calculations. This example will create three Items. A Date for the Previous Monday and a Date for the Previous Friday. These will be used to aggregate data between those dates. The aggregation uses the two custom dates and a date column from your data that can be used for aggregation. This is a basic way of doing a one week lookback without using filters or creating parameters.
Using the Following you can copy paste into a new calculated item to create a static Data Element for The previous Monday's date.
TreatAs(_Date_, ( ( ( TreatAs(_Number_, DatePart(Now())) - DayOfWeek(DatePart(Now())) ) + 2 ) - 7 ))
This code will create a date for the previous Friday.
TreatAs(_Date_, ( ( ( ( TreatAs(_Number_, DatePart(Now())) - DayOfWeek(DatePart(Now())) ) + 2 ) - 7 ) + 4 ))
TreatAs(_Date_, ( ( ( ( TreatAs(_Number_, DatePart(Now())) - DayOfWeek(DatePart(Now())) ) + 2 ) - 7 ) + 4 ))
Used together to create a custom calculation
In the following code date_str is a column from the data loaded to VA.
IF ( TreatAs(_Number_, TreatAs(_Number_, 'DATE_STR'n))
BetweenInclusive(TreatAs(_Number_, 'PrevMonday'n), TreatAs(
_Number_, 'PrevFriday'n)) )
RETURN 'CompletedAppointments'n
ELSE 0
IF ( TreatAs(_Number_, TreatAs(_Number_, 'DATE_STR'n))
BetweenInclusive(TreatAs(_Number_, 'PrevMonday'n), TreatAs(
_Number_, 'PrevFriday'n)) )
RETURN 'CompletedAppointments'n
ELSE 0
