Prompt Value

[former_member402770]

Hi Experts:

In

BO Webi:

  User interested in seeing the blank entries for fiscal period prompt.

For this:

Iam trying to capture the system date in the userresponse function if prompt not answered and i need to compare with below client calendar table to derive the current fiscal period:

Ex 1:  Year = 2015 Mon =1 Day =24

This means till jan 24th  its Client fiscal period which is 001/2015

Ex 2: Year = 2015  Mon =2 Day =21

This means last month jan 25th  onwards till feb 21st  it is Client period 2. 002/2015

Ex 3: Year = 2015  Mon =3 Day =28

This means last month feb 22nd onwards till March 28th it is Client period 3 003/2015....and so on..

Expected Output: In an empty cell: 003/2015

From mar 29th onwards, i have to display 004/2015 fiscal period in an empty cell and so on..

I have tried sofar captured the system date if user doesnot input the fiscal period prompt in an userresponse fn but could not able to proceed further.

Appreciate your kind help in derving the formula of fiscal period based on above examples from the userresponse fn..

Thanks,

-Dinya.

[Former Member · Accepted solution]

Hi Dinya,

YOu can do following steps:

1) Create variable :

Input_Date := if isnull(UserResponse"prompt") =1 then sysdate else userresponse"prompt"

2) Now compare Input_Date with the the dates you have asked

if  formatdate(Input_Date;"dd/mm/yyyy") <= formatdate("24/1"||"/"||year(Input_date);"dd/mm/yyyy") then "001/2015" elsif ( formatdate(Input_Date;"dd/mm/yyyy") >= formatdate("25/1"||"/"||year(Input_date);"dd/mm/yyyy")  and  formatdate(Input_Date;"dd/mm/yyyy") <= formatdate("21/2"||"/"||year(Input_date);"dd/mm/yyyy") ) then "002/2015" else "003/2015"

Hope this will work

[former_member402770 · Accepted solution]

Hi Swapnil,

  Thanks for your response. In your UserResponse "Prompt" the captured values is 201503 (Yearmonth) instead 20150317.

Now i need to compare input date from your above formula Modified one -> (Input_Date := if isnull(UserResponse"prompt") =1 then sysdate else 201503) with the dates i have provided above.

the challenge is on your format date formula as one in sysdate format if user input empty and other is yearmonth format if user inputs some value.

The other challenge is this output display of fiscal period is dynamic say if the year changes, the day and Month shifts accordingly based on BW Calendar table. Will your formula still works.

Hope you got it and any help on this would be much helpfull.

Thanks,

-Dinya

[Former Member · Accepted solution]

Ok I got your problem you can go for following solution which is bit complex.

if isnull(UserResponse"prompt") =1  then

if  formatdate(sysdate;"dd/mm/yyyy") <= formatdate("24/1"||"/"||year(sysdate);"dd/mm/yyyy") then formatdate(sysdate;"mmm/yyyy")

elsif ( formatdate(sysdate;"dd/mm/yyyy") >= formatdate("25/1"||"/"||year(sysdate);"dd/mm/yyyy")  and  formatdate(sysdate;"dd/mm/yyyy") <= formatdate("21/2"||"/"||year(sysdate);"dd/mm/yyyy") )

then "002/"||formatdate(sysdate;"yyyy")

elsif ( formatdate(sysdate;"dd/mm/yyyy") >= formatdate("22/2"||"/"||year(sysdate);"dd/mm/yyyy")  and  formatdate(sysdate;"dd/mm/yyyy") <= formatdate("28/3"||"/"||year(sysdate);"dd/mm/yyyy") )

then "003/"||formatdate(sysdate;"yyyy")

elsif

( formatdate(sysdate;"dd/mm/yyyy") >= formatdate("29/3"||"/"||year(sysdate);"dd/mm/yyyy")  and  formatdate(sysdate;"dd/mm/yyyy") <= formatdate("31/3"||"/"||year(sysdate);"dd/mm/yyyy") )

then "004/"||formatdate(sysdate;"yyyy")

else

formatdate(sysdate;"mmm/yyyy")

else

userresponse"prompt"

[former_member402770 · Accepted solution]

Hi Swapnil,

   Thanks, i try this meanwhile can you kindly respond to my second challenge which i mentioned.

The above formula works for static BW Table requirement for this year, Whereas how this formula still be changed according to the dynamic needs of every calendar year as we would be seeing an shift in days not the above days as i mentioned for example for this calendar year..

For this year 2015:

Start Jan  Date = 01/01/yyyy

End Date =   24/01/yyyy

From this end date we have to derive the remaining months end days for this year...

By the way End Date varies for every year(eg:2014,2016..) based on BW fiscal calendar table. How this end date can be arrived in  formula variable to apply to your above formula ..means

DayNumberofYear([sys date]) should check with the arrived End Date formula variable to apply to our below formula:

if isnull(UserResponse"prompt") =1  then

if  formatdate(sysdate;"dd/mm/yyyy") <= formatdate(".........

Hope you understood the sys date should check with BW calendar table for dynamic year needs.

Thanks,

-Dinya.

[Former Member · Accepted solution]

Hi,

Can you please let me know how you are calculating BW fiscal year.

I would require the whole logic with example so that I can help.

[former_member402770 · Accepted solution]

Hi Swapnil,

  Sorry for the late reply as i was checking the logic and surprisingly what is hapening in BW Side is there are loading data from ECC. So there isnt calculation on their side..But is it possible to try this logic in BO Side capturing the fiscal calendar below:

2015:

Ex 1:  Year = 2015 Mon =1 Day =24

This means till jan 24th  its Client fiscal period which is 001/2015

Ex 2: Year = 2015  Mon =2 Day =21

This means last month jan 25th  onwards till feb 21st  it is Client period 2. 002/2015

Ex 3: Year = 2015  Mon =3 Day =28

This means last month feb 22nd onwards till March 28th it is Client period 3 003/2015....and so on.

2014:

Ex 1:  Year = 2014 Mon =1 Day =25

This means till jan 25th  its Client fiscal period which is 001/2014

Ex 2: Year = 2014  Mon =2 Day =22

This means last month jan 26th  onwards till feb 22nd  it is Client period 2. 002/2014

Ex 3: Year = 2014  Mon =3 Day =29

This means last month feb 23nd onwards till March 29th it is Client period 3 003/2014....and so on.

I think if we could arrive the Start and End Date for each fiscal year Jan Month/Date, can we derive the remaining Months based on the Jan End Month/Date. But iam little bit puzzled of how to progress to get the current Fiscal Period if user doesnt input on the Fiscal Period prompt but still in the BO Side should be able to achieve with the last execution date of the report comparing based on the above Fiscal table..

Thanks,

-Dinya.

[Former Member · Accepted solution]

I think your logic is customized one.

So you have to handle whole logic at back end i.e BW.

Because in WEBI you can't be able to create such customized logic or you will end up with very complex formula.

Thanks.

Swapnil

[former_member402770 · Accepted solution]

Hi swapnil,

  Let me clearly explain you briefly, think this could be achievable the only think is i need some hands to help me to arrive this initial step by using webi formulas.

The calendar is 435 fiscal week base..

Step which needs your help

1) Start Date of every fiscal year, say  according to the system date it falls in thursday below is the snapshot.

so based on the mentioned calendar 435 fiscal week base, my first number 4 fiscal weeks- > Start 1st Month of any fiscal year here 1st 2015 and my end date week by referring above is 24.

3 fiscal weeks ->  for second Month starts on 25th Jan and ends by 14th feb 2015

5 fiscal weeks -> fiscal week starts 15th Feb 2015  and ends by 21st mar 2015

...for remaining 435 base we are have to derive based on webi formula for below ones:

fiscal week of every year like 435 base in every quarter

start date/day of fiscal week in that year

end date/day of fiscal week in that year.

Can we able to achieve this arrive the start and end date of every month based out of fiscal week

base 435 and compare the report last execution date with our derived start and end date dates to get the current fiscal periods.

Thanks,

-Dinya.

[former_member402770 · Accepted solution]

Hi Swapnil,

  Kindly help me on below thread or should i have to open new thread.

Thanks,

-Dinya.