How to display previous month and last 12 month?

[system]

Dear All Guru,

I have a report in which i need to show previous month and last 12 month data based on a UserResponse from a month selection.

Report Scenario Example:

User was ask to select [Successful Date] from the prompt which using ‘BETWEEN’ (From and To). Based on the selection, e.g

1 Nov 2013 to 31 Nov 2013.

Based on the selection above, i should show the result for previous month and last 12 months. Example

1. Previous Month = October 2013 to Nov 2013 result (1 October 2013 - 31 Nov 2013)

2. Last 12 month = Nov 2012 - Nov 2013 (1 Nov 2012 - 31 Nov 2013)

I am wondering:

1. How should i write the previous month and last 12 month variable based on UserResponse?

2. How do i filter it based on this variable

Regards,

Joe

---

Josephtys (BOB member since 2013-11-14)

[system]

Hi,
It seems to me that you´ll need to rebuild your query if you want to consider previous month. Your query justa returns data from Start Date through End Date, then you will have only the data belongin to this date interval.

Rogerio

---

rgoulart (BOB member since 2011-08-21)

[system]

You will need to create condition object at universe level to get the last 13 months something like this

Successful Date >
select add_months(max(successful date),-13) from table where successful date between @prompt(‘From Date’…) and @Prompt(‘To Date’…)
and
Successful Date] <=@Prompt(‘To Date’…)

and on your report level identify the previous month and last 12 months.

---

Prashu (BOB member since 2005-08-17)

[system]

Hi Prashu,

Just want to clarify with you.

If i want to get previous month, i should write this @prompt filtering like this:

Successful Date >
select add_months(max(successful date),-1) from table where successful date between @prompt(‘From Date’…) and @Prompt(‘To Date’…)
and
Successful Date] <=@Prompt(‘To Date’…)

Last 12 month will be:
Successful Date >
select add_months(max(successful date),-12) from table where successful date between @prompt(‘From Date’…) and @Prompt(‘To Date’…)
and
Successful Date] <=@Prompt(‘To Date’…)

After i get this two filter created.
Then in the report level. I will filter in this way:

Previous Month = 1
Last 12 Month = 12

Or just drag this two filter will do.

There are something i am quite confuse as well. When you use @prompt, do you still need to create a prompt filtering in the webi? I have no idea how it work

Regards,
Joseph

---

Josephtys (BOB member since 2013-11-14)

[system]

Just create one conditional prompt filter which will bring last 12 months of data. Out of this 12 months calculate the last month (Latest Month-1) at report level.

---

Prashu (BOB member since 2005-08-17)

[system]

Can you give me some example. I am quite new to Webi.

Regards,
Joseph

---

Josephtys (BOB member since 2013-11-14)

[system]

Ask your universe designer to create condition to fetch last 12 months based on user input/prompt

Successful Date  between add_months(to_date(@prompt('enter date in yyyymmdd)','A',,mono,free),"yyyymmdd"),-11) 
and 
to_date(@prompt('enter date in yyyymmdd)','A',,mono,free),"yyyymmdd")

---

Prashu (BOB member since 2005-08-17)

[system]

Hi Prashu,

If i add the @prompt below, will it work for the user if they want to schedule the report based on the 1st day of the next month, previous month and etc?

Regards,
Joseph

---

Josephtys (BOB member since 2013-11-14)

[system]

Joseph, to play with Date prompts, this will be very useful to you-

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

aniketp (BOB member since 2007-10-05)

[system]

Correct me if i am wrong. I am just trying to code all the date filter in universe.

Hi everyone,

is it ok for me to write this way.

dbo.DM_EXAM.COMPLETED_DATE 


between Case @Prompt('Enter start Date:','A',,MONO,FREE) 


when 'CURRENT MONTH' then DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 


when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 


when 'LAST 12 MONTH' then DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))


when 'YESTERDAY' then DATEADD(d,-1,GETDATE()) 


end 


and 


Case @Prompt('Enter End Date:','A',,MONO,FREE) 


when 'LAST WEEK' then getdate() 


when 'CURRENT MONTH' then DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 


when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 


when 'LAST 12 MONTH' then DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))


when 'YESTERDAY' then Getdate() 

end

For last 12 month coding, i am not sure whether i did i right or not.

Regards,

Joseph

---

Josephtys (BOB member since 2013-11-14)