how to achieve CY and LY result

[former_member196948]

Hi All

Wishing a very Happy Diwali  to you all.

I have stuck in 1 scenario, there data is like below.

from two different query i am getting current year and last year data.

qry 1				qry 2
Country	Month Key	Monthly Sales		Country	Month Key	Monthly Sales
UAE	201501	14,146,634		UAE	201401	13,635,027
UAE	201502	12,099,469		UAE	201402	11,617,266
UAE	201503	12,767,492		UAE	201403	12,319,683
UAE	201504	13,220,007		UAE	201404	11,779,896

My requirement is below

Country	Month Key	Monthly Sales	LY Monthly Sales
UAE	201501	14,146,634	13,635,027
UAE	201502	12,099,469	11,617,266
UAE	201503	12,767,492	12,319,683
UAE	201504	13,220,007	11,779,896

i am using sap Business objects 4.1 sp 5

Thanks in advance.

Regards

Jeet

[amitrathi239]

Hi,

for the Final table first merge the Country object and use in the table.for LY Monthly Sales object to use Force merge function to display values in the final table.

Sample variable=ForceMerge([Query 1].[Value])

Amit

[former_member196948]

Hi Amit,

I have tried the same but i am getting below result

formula: ForceMerge([Object name])

Month Key		Monthly Sales			LY mly sls after use force merge		Month Key		LY mly sls original
201501		16802755.77			13,977,713		201401		15,832,844
201502		14494529.55			13,730,983		201402		13,730,983
201503		15296658.41			14,331,928		201403		14,515,768
201504		15658631.98			15,831,112		201404		13,792,141
Sum:		62252575.71			57,871,735		Sum:		57,871,735

data sequence getting change..

any suggestion why it is getting change..

Regards,

Ranjeet

[amitrathi239]

Hi,

other option is  create two separate tables.

First table:Month Key,Monthly Sales

Second Table:Previous Month Key,LY Original sales

Hide the Previous Month key column in the table.

After that use Align->Relative position on second table to bound the position with first table.

See example.Here in the screenshot Block 1 is the First table.

Amit

[former_member196948]

Hi Amit

actually I requirement is based on this

i need to calculate R/H % (current year - last year)/ last year. due to my requirement is like this

Regards,

Ranjeet

[amitrathi239]

Hi,

Check with force merge function.check both measure objects are in same as number data type.Also try to sort the  monthkey by ascending/descending order.

Amit

[Former Member]

Hi,

another approach would be

1 - merging both queries on [country] and [month key]

2 - Create two dim variables [year] = left(""+[month key];4) and month =right(""+[month key];2)

3 - Create two measure variables [Last year value] = Sum([value] in ([month]) where ([year]="2014") )

[Current year value] = Sum([value] in ([month]) where ([year]="2015") )

4 - [r/h %] = (([current year value] - [last year value])/[last year value] ForaEach ([month key]))

Regards,

Rogerio