how to restrict data

[former_member196948]

Hi All,

I have an query,

i have data like below

Quarter                Amount

1Q 2008	1098046
2Q 2008	1086064
3Q 2008	1070520
4Q 2008	1035790
1Q 2009	1020156
2Q 2009	1018464
3Q 2009	1017317
4Q 2009	1013442
1Q 2010	1007551
2Q 2010	998727
3Q 2010	989713
4Q 2010	983499
1Q 2011	979105
2Q 2011	980793
3Q 2011	986718
4Q 2011	993552
1Q 2012	1001101
2Q 2012	1005139
3Q 2012	1001720
4Q 2012	989727
1Q 2013	985878
2Q 2013	1138789
3Q 2013	1144091
4Q 2013	1144487
1Q 2014	1140507

and i want output with below query

Output : two year period of  data

if report run on Jan 2014

it should reflect Jan 2012-Dec. 2013 data

1Q 2012	1001101
2Q 2012	1005139
3Q 2012	1001720
4Q 2012	989727
1Q 2013	985878
2Q 2013	1138789
3Q 2013	1144091
4Q 2013	1144487

for next run

if report run on Apr 2014

it should reflect April  2012 – March 2014 data

2Q 2012	1005139
3Q 2012	1001720
4Q 2012	989727
1Q 2013	985878
2Q 2013	1138789
3Q 2013	1144091
4Q 2013	1144487
1Q 2014	1140507

and so on....

how can i achieve this

please advice

Thanks in advance

Ranjeet  

[saurabh_sonawane · Accepted solution]

Hi RANJEET,

can u try this

create this dimension

month=ToDate([Cal. year / month];"MM.yyyy")

month -1=RelativeDate([month];-26)

final month -365=RelativeDate([month];-365)

create a measure

amountfinal = amount where [Cal. year / month] >= final month -365 and [Cal. year / month] <=month -1

[former_member196948 · Accepted solution]

Hi All,

I found the solution of my query.

I have to create some variable for finding the output

Variables:

variable Name                    syntax                                                       result

1)V_Todate :                =ToDate([month];"yyyyMM")                              for converting into date format

2)V_Currentdate           =CurrentDate()                                                  current date

3)V_Relativedate          =RelativeDate([V_Currentdate];-1)                       previous date

4) V_monthbetween     =MonthsBetween([V_todate];[V_Relativedate])     no of month between two                                                                                                          dates

then i applied filter on V_monthbetween with (Less than or equal to 24) for restricting the the data for 2 years

Thanks to all who replied or viewed my query

Ranjeet

[saurabh_sonawane · Accepted solution]

great bro

my solution help u or not????

[former_member196948 · Accepted solution]

Hi Saurabh,

your idea didn't work, but while working with your solution. i got this idea that i can with Current date function and achieve that.

So thanks again

Ranjeet

[saurabh_sonawane · Accepted solution]

ok  

[Former Member]

Ranjeet,

Create an object at universe level with the following syntax.

End_Quarter = Substr([Quarter];1;2) +" " +  (ToNumber(Substr([Quarter];4;4))+2) // Quarter after 2 Years from selected quarter.

Now in WEB I, create a prompt on Quarter...

This will give you 2years data based on selected quarter.

[former_member196948]

Hi Sujitha

Thanks for the reply ,

but there is a change in query business has changes the data from quarter to month and the format of data also

the new format is given below

Month   amount

201101	327309
201102	326420
201103	325376
201104	326139
201105	326831
201106	327823
201107	328566
201108	328613
201109	329539
201110	330022
201111	331741
201112	331789
201201	332785
201202	334111
201203	334205
201204	334990
201205	335531
201206	334618
201207	334823
201208	333777
201209	333120
201210	331533
201211	329858
201212	328336
201301	327673
201302	329045
201303	329160
201304	379225
201305	379700
201306	379864
201307	379926
201308	382183
201309	381982
201310	381419
201311	383919
201312	379149
201401	381057
201402	379749
201403	379701

and i want output with below query

Output : two year period of  data

if report run on Jan 2014

it should reflect Jan 2012-Dec. 2013 data

201201	332785
201202	334111
201203	334205
201204	334990
201205	335531
201206	334618
201207	334823
201208	333777
201209	333120
201210	331533
201211	329858
201212	328336
201301	327673
201302	329045
201303	329160
201304	379225
201305	379700
201306	379864
201307	379926
201308	382183
201309	381982
201310	381419
201311	383919
201312	379149

for next run

if report run on Apr 2014

it should reflect April  2012 – March 2014 data

201204	334990
201205	335531
201206	334618
201207	334823
201208	333777
201209	333120
201210	331533
201211	329858
201212	328336
201301	327673
201302	329045
201303	329160
201304	379225
201305	379700
201306	379864
201307	379926
201308	382183
201309	381982
201310	381419
201311	383919
201312	379149
201401	381057
201402	379749
201403	379701

please advice how to solve this issue

Thanks in Advance

Ranjeet

[saurabh_sonawane]

change the date  format as

MMMM yyyy

it will display output as

April 2014

if u want Apr 2014 then

MMM yyyy

[former_member196948]

Hi Saurabh,

Thanks for reply but my query  is how to restrict the data as below

Output : two year period of  data

if report run on Jan 2014

it should reflect Jan 2012-Dec. 2013 data

for next run

if report run on Apr 2014

it should reflect April  2012 – March 2014 data

for data part please go through my previous post to Sujitha

Thanks in advance

Ranjeet 

[saurabh_sonawane]

he r u get cal day in ur query

[former_member196948]

FORMAT IS LIKE "YYYYMM"  ONLY NO DAY

RANJEET

[former_member182346]

Hi,

Create a user exit variable.

Logic : whatever value entered by user say period 0042014, Program will fill the LOW and HIGH value as

HIGH = <user entered period> - 1 (make 004 -1 = 003) and use same yr. Special care for Yr if period is like 0012014

LOW = <user entered period> - 25 ( keep the period value same i.e. 004, where as reduce the year by 2 i.e.2014-2 = 2012)

OPTION = BT

take help from ABAPer to get logic works for you.

You may need to consider the user entered period like 0012014 to get the range of data with year change.

Thank-You.

Regards,

VB