Date Logics

[former_member402770]

Hi,

  I had an requirement to display date as an report header like 9+3 (forecast and actuals) examples.

Date is an webi input prompt:  user inputing 20150315

where Mon = 03 and day = 15..

Fiscal start and end date are based out of fiscal calendar derived in webi.. where start date = 3/1/15 and end date = 4/4/15

no what i would be wanted is to write an logic of Mon and Day to be compared to fiscal start and end date and derive my fiscal period which is 003/2015

if my input date is 20150228 = the report header should display 002/2015 and if

my input is 20150315 = = the report header should display 003/2015

there would be cases of start and end date differ for each fiscal calnedar and date input is dynamic now how can i arrive this to display which fiscal period in my report header

if user doesnt input anything last execution date base to derive the fiscal period by lookup up the snapshot table..

Thanks,

Dinya

[Former Member]

This is one way. Create three variables:

var 1  =FormatDate(ToDate(UserResponse("user input string:");"MM/dd/yyyy hh:mm:ss a");"MM/dd/yyyy")

var2  =Substr([var1];4;7)

var3=Concatenation("0";[var2])

[former_member402770]

Hi Avinash,

  Thanks for your response..My user input -> 20150315 where three varaiables created to extract:

Mon = 03

Day = 15

Year= 2015

Now here is my expected logic, i should be able to try the Mon,Day, Year logic with the snapshot if this could compare with the fiscal start eg: 3/1/15 and fiscal end for eg: 4/4/15 to derive the fiscal period say for eg: 003/2015

Note: Input date is dynamic and should look up the above snapshot fiscal calendar table to derive the fiscal period out of it based on fiscal start and end date from the above snapshot. Manytimes the fiscal start and end date ends before the system start/end month dates.

For eg: above fiscal end date 4/4/15 is our april month accoridng to system date but it belongs to the calendar table of client where in from say 4/5/15 starts only for May Month period..

Appreciate your help..

Thanks,

-Dinya.

[Former Member]

If I understand you correctly

You have to get the user response (input date) as listed in my previous response:

this should give you a date say 03/01/2015

Now what you can do is:

if (var1) between (fiscalstart;fiscalend) then 1 else 0

use the fiscal period where the value =1

did you get the idea?

[former_member402770]

Hi Avinash,

  Thanks for your response, yes i got your idea and tried applying to an formula varaiable:

while validating the formula varaiable it shows an error "invalid data type".

I think the var 1 you created needs to be in date time format? i.

Next i tried to use fiscal period by applying the formula:

if (var1) between (fiscalstart;fiscalend) then 1 else 0

It was actually showing output with 0 rows too but i would be requiring only fiscal period which falls between the input date..(20150301) alone..

Appreciate your help..

Thanks,

-Dinya..

[Former Member]

your var1 should work fine as it is..check your formula. i don't think datetime is required here.

"It was actually showing output with 0 rows too but i would be requiring only fiscal period which falls between the input date..(20150301) alone.."

can you elaborate with an example - if 2015-03-01 (YYYY/MM/dd) as input date.

where do you want this to fall in your example?

[former_member402770]

Avinash,

  My Input Date    "Enter Date" = 20150315..

                           var 1  = FormatDate(ToDate(UserResponse("Enter Date");"MM/dd/yyyy hh:mm:ss a");"MM/dd/yyyy")   // throws #error so i modified like below:

                           var1 = FormatDate(ToDate(UserResponse("Enter Date");"yyyyMMdd");"M/d/yy") = 3/15/15

                            => if ([var1]) between ([fiscalstart];[fiscalend]) then [fiscal period] else 0 means

3/15/15 falls in below 3/1/15 and 4/4/15 then 003/2015 else 0

Fiscal start and end date are based out of fiscal calendar derived in webi.. where fiscalstart = 3/1/15 and fiscalend = 4/4/15

Summarizing the above shall yield 003/2015 for the above fiscal start,end date and 0 also for other fiscal start and end date...

Output will be:

003/2105

0

Expected O/p:

003/2015

Thanks,

-Dinya