Based on End date i need to find Starting date in WEBI.

[Former Member]

hi Folks,

I have below requirements.

User want to select End date first: March 20 2015

once the user select date above mention, the start date should automatically trigger to march 01 2015.

Kindly suggest how to hard code on this to get start date based on end date.

user will select only end date of any particular month of give year.

Thanks in advance

ram    

[Former Member · Accepted solution]

Something along these lines should work for you:

Find last day of month [End Date]

=ToDate( LastDayOfMonth([EndDate]) ;"MM/dd/yyyy")..call it var1

Find day number in var 1 =DayNumberOfMonth([var1]) .. var2

var3 =If [var2]=31 Then RelativeDate([var1];-30) Else RelativeDate([var1];-29)

[Former Member · Accepted solution]

Hi Avinash,

Thanks for your valuable information and appreciate for all your works.

I mistakenly mention its report level, I want hard code for Query Level Filter.

Once the report refresh it should prompt to End Date: Any Date Range

Start date based on End date of the month that particular month.

Kindly suggest me.

Thanks

Ram

[Former Member · Accepted solution]

do you know how to create prompt at query level using your end date object?

[Former Member · Accepted solution]

Avinash,

I simple drop the Date Filed into query Filters and I gave Between Function for Start Date and End Date.

Problem is I don't know how to hard code in query level filters. Is there any way to get expected results under query level?

Please suggest me.

Thanks

ram    

[Former Member · Accepted solution]

you can just give end date as prompt in query filter..based on the end date you can convert start date to start of month

[Former Member · Accepted solution]

Avinash,

I'm sorry, can you please explain clear on How we can convert in query filters.

In Query Filters I gave Date Range and then How I can do conversion here in query?

Please suggest me steps if you dont mind.

Thanks

ram    

[Former Member · Accepted solution]

Hi Ram,

              please tell your requirement clearly

Regards

Dinesh

[Former Member · Accepted solution]

Hi Dinesh,

In Source Data I have date fields,

Business Requirement are:

1. Say suppose business user will select April 10 date as a end date in prompt.

2. User want to see April 01 date to April 10 date  date want to display output(it should be automatically the April 1st date wan to trigger here)

One more example:

Prompt:

End Date: Jan 27(Manual Selection)

Start Date: Jan 1st( Automatically need to select Jan 1st date in that particular month)

Ex: 3

End Date: March 17(Dynamic Selection)

Start Date:1st March (Static)

Thanks

ram

[Former Member · Accepted solution]

Hi..

You have to create universe level object called Start_Date = trunc(End_date;'MM');

which will get you first date of any month.

Ex. if end_Date is in date format and value for same is 28-Jan-2015

then value of start date = trunc('28-jan-2015','MM') = 1-jan-2015

I hope this will help you and its very simple.

Now you can use this start_Date in report in your condition.

Thanks,

Swapnil

[Former Member · Accepted solution]

Hi Swapnil,

Thank you, Since Source is from BW, I will request them to create a separate object for this Truncate value.

That is correct right.

thanks

ram

[Former Member]

Hi,

this should happens at query or report level ?

Regards,

Rogerio

[Former Member]

Hi Rogerio,

Thanks for your mail.

In report level.