Need Formula for Week, WTD, MTD, YTD

[system]

Hi Experts,

I am having some reports, In which I need to display Day, Week, Month, Week to Date, Month to Date, Year to Date.

I am using BOXiR2, Oracle 9i as back end.

I have searched entire forum, But I did not the proper solution to my requirement. Please let me know the logic.

NOTE:
I don’t have these columns in the DB, I need to create it in the Universe level.

Table Name: CUSTOMER
Coulumn Name: DATE

Based on the user selection in the report level, I need to display the report in Cross Tab as

                                                                          [b]  Week |    Prev. Month  |  WTD |  MTD |  YTD[/b]

Number of Customer 100 | 1000 | 100 | 1000 | 4000
Total Revenue 500 | 2000 | 2000 | 5000 | 7000

Assume user selects the date dynamically Prompt i.e May 22, 2009.

Week:
Eg: Monday through Sunday
Description: Based on Today’s date, select the Monday of the previous week for the start date and Sunday will be the end date.
Example:
If Today is May 22, 2009
The result would be, Monday = May 11, 2009, Sunday = May 17, 2009

WTD:
If Today is May 22, 2009
I need a result WTD = May 18, 2009 to May 22, 2009

MTD:
If Today is May 22, 2009
I need a result MTD = May 1, 2009 to May 22, 2009

YTD:
If Today is May 22, 2009
I need to display YTD = Jan 1, 2009 to May 22, 2009

Prev. Month:
If Today is May 22, 2009
I need to display Prev. Month = Apr 1, 2009 to Apr 30, 2009

Appreciate for your quick response…

Thanks,
vaasu.

---

vasu237 (BOB member since 2007-02-19)

[system]

I’m surprised you didn’t find anything in your searches since this has been discussed many times.

Try this Link and use BETWEEN SYSDATE to get todays date.

---

dessa (BOB member since 2004-01-29)

[system]

Hello Dessa,

I found 1 post with the same requirement, but there is no reply for that post…

Vaasu

---

vasu237 (BOB member since 2007-02-19)

[system]

Just as brief example (Oracle syntax):

YTD measure based on today (=sysdate)

SUM
(
   CASE WHEN (Sometable.Fact_Date) between TRUNC (sysdate , 'yyyy') and sysdate 
      THEN FactTable.Measure
   END
)

MTD measure based on today (=sysdate)

SUM
(
   CASE WHEN (SomeTable.Fact_Date) between TRUNC (sysdate , 'mm') and sysdate 
      THEN FactTable.Measure
   END
)

Note:
Oracle’s TRUNC function truncates the date, for example TRUNC (sysdate, ‘mm’), truncates the date to the first day of the current month.
TRUNC (sysdate, ‘yyyy’) truncates today’s date to the first day of the current year. TRUNC (sysdate) truncates today’s date to midnight (00:00:00). Oracle dates always contain the timestamp (dd.mm.yyyy hh:mi:ss, for example: 31.12.2009 08:55.34)

---

Andreas (BOB member since 2002-06-20)

[system]

Hello Andreas,

Thanks for your reply, I will implement in this way and let you know…

Suggestions are always welcome…

Vaasu.

---

vasu237 (BOB member since 2007-02-19)

[system]

Hello,

I have implemented the logic in this way and working fine.

Based on the user selection, it has to display the following…

Date:

to_char(DATE,'DD/MM/YYYY')

Month:

to_char(DATE,'MM/YYYY')

Week:

to_char(TRUNC(DATE,'IW'),'DD/MM/YYYY')

Day:

Sum(cost) where trunc(DATE) = to_date(@prompt('Enter Date:','A',....User:0),'DD/MM/RR')

WTD:

Sum(Cost) where trunc(DATE) BETWEEN trunc(to_date(@prompt('Enter  Date:','A','.......,,User:0),'DD/MM/RR'),'IW') AND to_date(@prompt('Enter  Date:','A','.........,User:0),'DD/MM/RR')

MTD:

Sum(Cost) where trunc(DATE) BETWEEN trunc(to_date(@prompt('Enter  Date:','A','.......,,User:0),'DD/MM/RR'),'MM') AND to_date(@prompt('Enter  Date:','A','.........,User:0),'DD/MM/RR')

YTD:

Sum(Cost) where trunc(DATE) BETWEEN trunc(to_date(@prompt('Enter  Date:','A','.......,,User:0),'DD/MM/RR'),'RR') AND to_date(@prompt('Enter  Date:','A','.........,User:0),'DD/MM/RR')

Hope this will be useful…

Thanks,
Vaasu.

---

vasu237 (BOB member since 2007-02-19)

[system]

Hi,

How are you doing this? I tried your logic. I get ‘No corrsponding data’ error if I try to show MTD, YTD and Daily sales on one block. I tried merge dimensions too.
I have a similar requirement. User enters the date at the prompt and the report should show Month to the user entered date, Year to the user entered date etc…

I created the MTD Measure object as follows: When I try to use it, I get ‘no corresponding data’ error.

Select SUM(AMOUNT)

Where

@Select(Test\Settled_Date) between @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0) and @Select(Test\MTD Date)

The above MTD_Date object gives the startdate of the user prompt date

---

shamanth (BOB member since 2007-03-01)

[system]

Hi,

Please find the attachement. But, this is used by SQLSERVER database functions. Change according to your DB.
ut007.pdf (54.0 KB)

---

sreeb (BOB member since 2009-01-05)

[system]

I tried something different but didn’t work. I am thinking of creating a derived table. But I don’t want to make things complex. Please see my logic below:

Settled Date_ is an Object that is referencing a date column (Test.SETTLED_DATE ) in the table.

I created a couple of date objects and two measure objects as follows:

MTD Date:
Select
trunc(@Select(Test\Settled Date_),‘mm’)

Where
Test.SETTLED_DATE = @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
#######################################
YTD Date:
Select
trunc(@Select(Test\Settled Date_),‘yyyy’)
Where
Test.SETTLED_DATE = @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
#######################################

MTD
Select
Sum((CASE WHEN @Select(Test\Settled Date_) between
@Select(Test\MTD Date) and @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
THEN
DECODE
(CODE, ‘A’, AMOUNT,‘C’, (AMOUNT*(-1)),0
)
ELSE 0
END
)
)

##########################################

YTD
Select
Sum((CASE WHEN @Select(Test\Settled Date_) between
@Select(Test\YTD Date) and @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
THEN
DECODE
(CODE, ‘A’, AMOUNT,‘C’, (AMOUNT*(-1)),0
)
ELSE 0
END
)
)
###########################################
There is no WHERE clause in the above measures. I am not sure how objects behave if I use @prompts in SELECT pane of the object. I also tried creating similar prompts at Web Intelligence level. I got the same result.

I am getting the same values for both MTD and YTD when I pull the two measure objects on to one
report block with a dimension called Product

Thanks!

SG

---

shamanth (BOB member since 2007-03-01)

[system]

I just tried MTD and YTD from different dataproviders on Web Intelligence Query pane /Merge Dimensions and I am still getting the same values for both MTD and YTD.
I actually have couple of more measures to calculate; Prior YTD and Daily Sales.

-SG

---

shamanth (BOB member since 2007-03-01)

[system]

I am still getting the same values for both MTD and YTD.

Use the qualified measures, from the relevant queries, below assumes WTD and MTD query name.

e.g.

[WTD].[Your Measure Name] = WTD Sales
[MTD].[Your Measure Name] = MTD Sales

---

Mak 1 (BOB member since 2005-01-06)

[system]

That is exactly what I am doing… But I am still getting the same values for MTD and YTD
Actually, I don’t want to do multiple data providers. I want to do this with single dataprovider and do just drag and drop to the report. I tried to do this with the above SQL and @Prompts. But didn’t work. Is there anything wrong with my syntax?

-SG

---

shamanth (BOB member since 2007-03-01)

[system]

Select 
trunc(@Select(Test\Settled Date_),'mm') 
Where 
Test.SETTLED_DATE = @prompt('Enter SettledDate:','D',,Mono,Free,Persistent,,User:0) 

You are saying that the Month to Date and your YTD is equal to one date / day, i.e. look at your dimensions:-

Test.SETTLED_DATE = @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)

This operator should be between, you can use this in conjunction with @ prompt, sysdate and CASE in a select statement, to create the measure.
Your syntax is full of @Select so I cannot tell what else it is supposed to be doing.

Just out of interest did you actually read Andreas post, above?

---

Mak 1 (BOB member since 2005-01-06)

[system]

Are you refering to the Where clause of MTD Date and YTD Date dimension Objects? Like this…?
Test.SETTLED_DATE between trunc(@Select(RISReporting\Settled Date_),‘mm’)) and @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)

How SYSDATE helps me here? The user can pass any date from the calender. It can be last year too. Depending on that I have to show MTD and YTD calculations on the report.

Where is Andreas post? I tried Andreas logic as mentioned above: CASE statements.

I changed the date dimension prompts as above (Used ‘between’)and I stll see the same values for MTD and YTD

---

shamanth (BOB member since 2007-03-01)

[system]

if i corretly understood this are the dates

this function will return the year values of a date object for
{ fn year (Financial_period.fp_start)}
fn month(Financial_period.fp_start)}
fn weekFinancial_period.fp_start)}
datepart(qq,financial_period.fp_start) this function will return quater

---

harrypintous (BOB member since 2010-02-05)

[system]

I tried another thing. I am using the below SQL in the SELECT clause of he MTD measure. The designer parses OK. But the infoview gives me
an oracle error: ORA 12801: Error Signalled in Paralell Quer Server P025
and ORA 01843: Not a valid month.

Sum((CASE WHEN @Select(Test\Settled Date_) between
trunc(to_date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0),‘MM/DD/YYYY’),‘MM’) and Trunc(to_date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0), ‘MM/DD/YYYY’))
THEN

DECODE
(MTR_TRANS_CODE_OVERRIDE, ‘A’, mtr_AMOUNT,
‘C’, (mtr_AMOUNT*(-1)),
0
)
ELSE 0
END
)
)

---

shamanth (BOB member since 2007-03-01)

[system]

You are closer :), I’ll give you an example, for YTD, try something like:-

SUM
(
CASE
   WHEN TableName.Settled Date >=
    TRUNC (To_Date(@Prompt ('Enter SettledDate:'', 'A',,mono,free),'mm/dd/yyyy'),'yyyy')
    AND TableName.Settled Date <
    TRUNC (To_Date(@Prompt ('Enter SettledDate:'', 'A',,mono,free), 'mm/dd/yyyy'))+1
   THEN DECODE
(MTR_TRANS_CODE_OVERRIDE, 'A', mtr_AMOUNT,
'C', (mtr_AMOUNT*(-1)),
0
)
ELSE 0 
END
)

Combine the Add months function and you should be able to work something out for MTD.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Please see the below

---

shamanth (BOB member since 2007-03-01)

[system]

Mak,

I am stuck with an Oracle error. Designer parses the YTD object fine. I am gettting ORA 1830 error at Infoview. I guess it is to_date function. I think @prompts convert the output into character. Am I correct?

SELECT:

Sum(
DECODE(Table.MTR_TRANS_CODE_OVERRIDE, ‘A’, AMOUNT,‘C’, (AMOUNT*(-1)),
0)
)

WHERE:

Table.SETTLED_DATE>=
trunc(to_date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0),‘DD/MM/YYYY’),‘YYYY’)
and Table.MTR_SETTLED_DATE <
trunc(to_date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0),‘DD/MM/YYYY’))+1

-SG

---

shamanth (BOB member since 2007-03-01)

[system]

Sorry,

This is not my original post. So, I created a new one to avoid confusion. I am getting the same resuls for MTD and YTD on Web-I report. I also tried with conditional objects

Here is what I did:

Created three Date Objects:
Settled_date
Select Caluse:
TABLE.MTR_SETTLED_DATE

YTD:
Select Clause:
@Select(RISReporting\Settled_Date)
Where Clause:
@Select(RISReporting\Settled_Date) = trunc(To_Date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0),‘dd/mm/yyyy hh24:mi:ss’),‘yyyy’)

MTD:
Select Clause:
@Select(RISReporting\Settled_Date)
Where Clause:
@Select(RISReporting\Settled_Date) = trunc(To_Date(@prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0),‘dd/mm/yyyy hh24:mi:ss’),‘mm’)

YTD MEasure:
Select Clause:

Sum((CASE WHEN @Select(RISReporting\Settled_Date) between
@Select(RISReporting\YTD Date) and @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
THEN

DECODE
(MTR_TRANS_CODE_OVERRIDE, ‘A’, mtr_AMOUNT,
‘C’, (mtr_AMOUNT*(-1)),
0
)
ELSE 0
END
)
)

MTD Measure:
Sum((CASE WHEN @Select(RISReporting\Settled_Date) between @Select(RISReporting\MTD Date)and @prompt(‘Enter SettledDate:’,‘D’,Mono,Free,Persistent,User:0)
THEN

DECODE
(MTR_TRANS_CODE_OVERRIDE, ‘A’, mtr_AMOUNT,
‘C’, (mtr_AMOUNT*(-1)),
0
)
ELSE 0
END
)
)

Thanks!

SG

---

shamanth (BOB member since 2007-03-01)