I had a few of my users reach out to me regarding relative date-time calculations. Since this is something developers use most of the time, I decided to list them down in a post.
Before we proceed further, I would like to note that there are multiple ways to perform these calculations. I have listed one of the methods I have used in the past below.
Note:
- Replace <INSERT DATE QUERY ITEM> with your date field.
- Replace <INSERT MEASURE QUERY ITEM> with your Measure field.
- Replace <date_prompt> with your date prompt.
WEEK TO DATE (WTD) calculation:
CASE
WHEN
( <INSERT DATE QUERY ITEM> BETWEEN _add_days( <date_prompt>, _day_of_week(<date_prompt>,1) +1 ) and <date_prompt> )
AND
( <INSERT DATE QUERY ITEM> = EXTRACT(MONTH ,<date_prompt>) )
THEN <INSERT MEASURE QUERY ITEM>
ELSE 0
END
MONTH TO DATE (MTD) calculation:
IF ( <INSERT DATE QUERY ITEM> BETWEEN _first_of_month (<date_prompt>) AND <date_prompt> )
THEN (<INSERT MEASURE QUERY ITEM>)
ELSE ( 0 )
QUARTER TO DATE (QTD) calculation:
IF (
<INSERT DATE QUERY ITEM> BETWEEN _make_timestamp(extract( year,<date_prompt>),1 ,1 ) AND _make_timestamp(extract( year,<date_prompt>),3 ,31 )
AND <INSERT DATE QUERY ITEM> <= <date_prompt> AND extract ( month ,<date_prompt> ) BETWEEN 0 and 3
)
THEN (<INSERT MEASURE QUERY ITEM>)
ELSE IF (
<INSERT DATE QUERY ITEM> BETWEEN _make_timestamp(extract( year, <date_prompt> ),4 ,1 ) AND _make_timestamp(extract( year, <date_prompt>),6 ,30 )
AND <INSERT DATE QUERY ITEM> <= <date_prompt> AND extract ( month , <date_prompt> ) BETWEEN 4 and 6
)
THEN (<INSERT MEASURE QUERY ITEM>)
ELSE IF (
<INSERT DATE QUERY ITEM> BETWEEN _make_timestamp(extract( year, <date_prompt> ),7 ,1 ) AND _make_timestamp(extract( year, <date_prompt>),9 ,30 )
AND <INSERT DATE QUERY ITEM> <= <date_prompt> AND extract ( month , <date_prompt> ) BETWEEN 7 and 9
)
THEN (<INSERT MEASURE QUERY ITEM>)
ELSE IF (
<INSERT DATE QUERY ITEM> BETWEEN _make_timestamp(extract( year, <date_prompt> ),10 ,1 ) AND _make_timestamp(extract( year, <date_prompt>), 12 ,31 )
AND <INSERT DATE QUERY ITEM> <= <date_prompt> AND extract ( month , <date_prompt> ) BETWEEN 10 and 12
)
THEN (<INSERT MEASURE QUERY ITEM> )
ELSE (0)
YEAR TO DATE (YTD) calculation:
IF
( <INSERT DATE QUERY ITEM> BETWEEN _make_timestamp(extract( year,<date_prompt> ),1,1) AND <date_prompt> )
THEN (<INSERT MEASURE QUERY ITEM>)
ELSE ( 0 )
I will create a sample report xml which contains the above calculations and attach it to this post. Send me a note if you need more information or have any questions.
How Business Intelligence Works 