Year values need to be merged

[former_member183330]

Hi Experts,

I Have year and month Columns,

I am using universe created on multi provider, one data provider is giving year numbers and months as month numbers but another data provider is giving # instead of year and month.

The year having the values like below:

Year         

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

#

And the Month having the values like below:

1

2

3

4

5

6

7

8

9

10

11

12

#

My requirement is when i select the month 1 then 1 and # need to be selected if i select 2 then 2 and # need to be selected like this for all months

and for year also when i select the year 2012 then 2012 and # need to be selected if i select 2013 then 2013 and # need to be selected like this when ever i take year selected year and # need to be selected.

I Have done for month the logic is =If([Month] InList("1";"#");1;If([Month] InList("2";"#");2)), like this I wrote for 12 months, but for year I am not able to get the logic.

Please give some inputs,

Thanks,

G Sampath Kumar

[former_member183330 · Accepted solution]

Hi Pls,

Find the screen shot,

All I want is For every month in table I need to Add the amount which is coming for "#"

Foe example: Jan 1000, Feb 500, March 1500 ...... etc and the # value is 5000

So I want Jan 1000+5000, Feb 500+5000 March 1500+5000 like this.

G sampath Kumar

[sateesh_kumar1 · Accepted solution]

Hi Sampath,

You can write two variables to get  month # value and Year # value .

V1=nofilter([value] where ([calmonth]="#") )

V2=nofilter([value] where ([calmonth]="#"))

and add it with [Value] keyfigure

[former_member183330 · Accepted solution]

HI Satish,

I Have used below logic and it is not displaying any value

=NoFilter([ZMBW_Material]) Where([L01 Approval year]="#"And[L01 Calendar month]="#")

Any other inputs please.

Thanks,

G sampath kumar

[Former Member · Accepted solution]

Sampath,

The solution for this is not going to be easy considering you are showing result in cross tab. I think I am pretty close to cracking the code here but to explain the solution here in a logical way there are challenges. Can you provide a sample set of data and format you want to see in report for me to make the explanation easier.

[Former Member · Accepted solution]

Hi,

Sometimes, NoFilter won't work in the Block where Block filter is also applied.

Remove the Block filter, instead apply Input control to restrict the row/column #.

Now, apply the formula: =[ZMBW_Material]+([ZMBW_Material] Where([L01 Approval year]="#"And[L01 Calendar month]="#"))

Thanks.

[sateesh_kumar1 · Accepted solution]

Hi Sampath

there is small correction , try with below

=NoFilter([ZMBW_Material] Where([L01 Approval year]="#"And[L01 Calendar month]="#"))

[former_member183330 · Accepted solution]

Hi Ganesh,

I have taken the data from 2012 march to till month.

Based on Month and Year I have created A Variable, in this I will get previous year march and present year march to till month and other months are showing as extra values so I have hided the extra values by using filter The # value of month and year also coming under extra values, now based on variable measure values will be divided in table, So I wanted to add the # month and year values for every month

So if I apply the above formula the data is coming under extra values only not coming for all months which we are displaying as months.

Thanks,

G Sampath Kumar

[former_member183330 · Accepted solution]

Hi Mishra,

I am getting the data from two infosets which combined as multiprovider. Infoset1 having year and month values but Infoset2 giving the # for month and year. The measure is also coming from two infosets merging as a single column in report I want to display the data(Amount) for Infoset1 month wise and infoset2 # Amount need to be merge with every month value of infose1.

the months should be dynamic in report

Thanks,

G Sampath Kumar

[Former Member · Accepted solution]

Hi,

Kindly Post a Screenshot of the issue with the formula visible in the screenshot.

Thanks.

[Former Member · Accepted solution]

Hi,

To the best of my knowledge, it is not possible to achieve what you are trying to do. Assigning a value from another query didn't work for me in the past.

Please refer the below thread:

http://scn.sap.com/thread/3309270

Try to get the # values in the same query itself.

Thanks.

[former_member183330 · Accepted solution]

Hi Ganesh,

Please find the logic which i am using for month:

__________________________________________________________________________________

=If(MonthNumberOfYear(CurrentDate())InList(1;2;3)

And

[Calendar month]>=3

And

[Calender Year]

=

FormatNumber(ToNumber(FormatNumber(Year(CurrentDate())-1;"####")) ;"####")

And

[Calendar month]<=MonthNumberOfYear(CurrentDate())

And

[Calender Year]

=

FormatNumber(ToNumber(FormatNumber(Year(CurrentDate());"####")) ;"####")

;[Month1]+[Calender Year]

;If(

Not(MonthNumberOfYear(CurrentDate())InList(1;2;3))

And

[Calendar month]>=3

And

[Calender Year]

=

FormatNumber(ToNumber(FormatNumber(Year(CurrentDate());"####")) ;"####")

And

[Calendar month]<=MonthNumberOfYear(CurrentDate())

And

[Calender Year]

=

FormatNumber(ToNumber(FormatNumber(Year(CurrentDate());"####")) ;"####")

;[Month1]+[Calender Year]

;

If([Calendar month]=3

And

[Calender Year]

=

FormatNumber(ToNumber(FormatNumber(Year(CurrentDate())-1;"####"));"####")

;[Month]+[Calender Year];"Extra Values"

)

))

________________________________________________________________________________

# Month coming as error

Thanks,

G Sampath Kumar

[former_member183330 · Accepted solution]

I am using single query, please find the objects in screen shot in previous post

[former_member184594]

Hi,

Do you have 0CALMONTH and 0CALYEAR in both of your info providers? If you do, then why don't you assign for both on BW side instead of BusinessObjects?

[former_member183330]

Hi any body is there

[Henry_Banks]

hi i think your question is unclear..

[Former Member]

Do you want to use input control for Year/Month slection? or prompt for selection?