-
Notifications
You must be signed in to change notification settings - Fork 0
/
Cognos Analytics Dynamic Relative Date Time Calculations
56 lines (43 loc) · 2.24 KB
/
Cognos Analytics Dynamic Relative Date Time Calculations
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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 )