Convert 0FISPER to dd.MM.yyyy Format

[Former Member]

Hi,

I want to get the last day of the month that I have entered to Prompt as a 0FISCPER in Webi 4.1 SP4 with using BICS query.

Value of UserResponse is return as Month Year format, for example "JUL 2015".

How can I convert it to dd.MM.yyyy format (01.07.2015)?

So, I would be able to use the LastDayofTheMonth function to get the last day.

My best Regards,

[amitrathi239 · Accepted solution]

Hi,

use this.change the "userresponse var" part with your variable.

=LastDayOfMonth(ToDate([userresponse var];"MMM yyyy"))

it will give you the last day of the month.

Amit

[Former Member · Accepted solution]

Hi Amit,

Formula works for MAR ( MAR), NİS (APR) , MAY (MAY) , HAZ (JUN), TEM (JUL), EYL (SEP)

Formula does not work for OCK (JAN), ŞBT (FEB), AGU (AUG), EKM (OCT), KAS (NOV), ARL (DEC).

It gives ERROR message with months I have written above.

Why does it gives ERROR for some months, how can I solve it?

Best Regards,

Cihangir

[amitrathi239 · Accepted solution]

Hi,

How the values are coming in userresponse variable for Months where formula is not working?

Values are consistent like "JUL 2015" or coming in different format.

Amit

[Former Member · Accepted solution]

Hi Amit,

For example it gives an ERROR for last day of AGU 2015 (AUG 2015), but it works for last day of TEM 2015 ( JUL 2015) with same formula.

What is the problem, I did not understand?

Best Regards,

Cihangir

[amitrathi239 · Accepted solution]

Hi,

Check the Length of both the  values.

=Length([Userresponse])

or also try with this.

=LastDayOfMonth(ToDate([userresponse var];"Mmm yyyy"))

Amit

[former_member184594 · Accepted solution]

Hi Cihangir,

You are receiving error because your month names are in Turkish. WebI formulas work with English.

I did the followin formulas to get the last day of month in your case.

1. Create "Month Number" variable just like below:

=If Left([Takvim yılı / ay];3) = "OCK" Then "01"

ElseIf Left([Takvim yılı / ay];3) = "ŞBT" Then "02"

ElseIf Left([Takvim yılı / ay];3) = "MAR" Then "03"

ElseIf Left([Takvim yılı / ay];3) = "NİS" Then "04"

ElseIf Left([Takvim yılı / ay];3) = "MAY" Then "05"

ElseIf Left([Takvim yılı / ay];3) = "HAZ" Then "06"

ElseIf Left([Takvim yılı / ay];3) = "TEM" Then "07"

ElseIf Left([Takvim yılı / ay];3) = "AGU" Then "08"

ElseIf Left([Takvim yılı / ay];3) = "EYL" Then "09"

ElseIf Left([Takvim yılı / ay];3) = "EKM" Then "10"

ElseIf Left([Takvim yılı / ay];3) = "KSM" Then "11"

ElseIf Left([Takvim yılı / ay];3) = "ARL" Then "12"

2. Create "Year Number" variable like below:

=Right([Takvim yılı / ay];4)

3. Create "Month-Year" variable:

=[Year Number]+[Month Number]

4. Create "Month-Date" variable:

=ToDate([Month-Year];"yyyyMM")

5. Create "Last Day of Month" variable below:

=LastDayOfMonth([Month-Date])

[Former Member · Accepted solution]

Hi Zahid,

It works. Thank you very much.

My Best Regards,

Cihangir