How to get last date of the current month previous year in webi

[Former Member]

If curr date is 17/10/2017. i need 31/10/2016

[amitrathi239 · Accepted solution]

if your bo version is above bi 4.1 sp06 then use below.

=RelativeDate(LastDayOfMonth([curr date]);-12;MonthPeriod)

[Former Member · Accepted solution]

Thank you expert.working perfectly

I have another problem hope you can help me out.

I have managed to do an Extended Merge dimension works.

Q1&Q2 (Merged - Result Expected) Q1 Q2

111 111 111

122 122 122

121 121

232 232

But I need to write a single statement formula saying

IF Id both matches in Cust Id and Emp Id then "A" Eg (100,200)

IF Id Only in Cust Id Then "B" Eg(300 to 600)

If Id only in Emp Id Then "C" (700-900)

[amitrathi239]

use this.

=LastDayOfMonth([curr date])

[Former Member]

Hi Amit,

Today is 19/10/2017.so I want last date of current month in last year.

31/10/2016

[Former Member]

Hi,

Attached the screen shot. I have query from 2 DP. one from table(Material)and another from Excel(ITEM)

I am doing an extended merge, so i can get matching as well as unmatching record

The formula i am trying =If IsNull([LWVAL])<>0 Then "A" ElseIf IsNull([V_LastWeek_Data]<>0) Then "B" ElseIf IsNull([LWVAL])<>0 And IsNull([V_LastWeek_Data]<>0) Then "C" Else "NotDefined"

1) 111 contains in both table (Material& Excel)

2)121 only contains in Excel

3)123 only contains in Materialcapture1.jpg

[amitrathi239]

have you tried with creating LWVAL and V_LastWeek_Data variables as detail variable and associate with merged dimension?

[former_member182342]

Hi Sudarsan,

Please check the below formulas

year=todate(DayNumberOfMonth(Curr_date)+Month(curr_date)+relativedate(year([curr_date]);-1);dd/MM/yyyy)

Prev_date= Relativedate([year];-DayNumberOfMonth([year]))

Let me know if it works or not.

Regards,

Anish

[Former Member]

capture.jpg

Hi Anish,

I am getting an error

[former_member182342]

Hi Sudarsan,

Please use the below formulas:

year=todate(DayNumberOfMonth(Curr_date)+Month(curr_date)+Year(relativedate(([curr_date]);-DayNumberOfYear));dd/MM/yyyy)

Prev_date= Relativedate([year];-DayNumberOfMonth([year]))

Please let me know if it works or not.

Thanks,

Anish