last 5 weeks of data from history

[Former Member]

Hello all,

I am using BEx as data source and we have last 10 weeks of Quantity as below:

Country	Material	28.2014	29.2014	30.2014	31.2014	32.2014	33.2014	34.2014	35.2014	36.2014	37.2014	Total
CountryA	MatA	10	6	10	10	3	6	3	6	2	3	59
	MatB	3	3	15	3	2	10	2	10	6	2	56
	MatC	2	2	6	2	6	3	6	6	15	5	53
	MatD	8	5	8	5	8	2	8	8	6	2	60
	MatE	4	6	4	6	4	5	4	4	8	3	48

I am expecting top 3 materials on quantity for last 5 weeks of data only as as below.

Country	Material	Quantity
CountryA	MatC	35
	MatB	30
	MatD	26

FYI,

below is the 5 weeks of data from given above 10 weeks of data.

Country	Material	33.2014	34.2014	35.2014	36.2014	37.2014	Total
CountryA	MatA	6	3	6	2	3	20
	MatB	10	2	10	6	2	30
	MatC	3	6	6	15	5	35
	MatD	2	8	8	6	2	26
	MatE	5	4	4	8	3	24

Question is how to restrict quantity to last 3 weeks from given 10 weeks of data

Thank you,

suri

[former_member4998]

Hi

If Webi Report backend is Bex Q....

as best practice create time periods at Bex Q level and use directly in the WebI Report.

Time Periods...like Last Week, Last five Week,Current Week, Last Year ..etc

@ Bex Q levle- Last Week = On Info object 0CALWEEK with variable YV_CALLWEEK

calling ABAP program . The variable is offset in the Bex query designer with a hardcoded -1.

Last five Week=On info object0CALWEEK with variable YV_CALLWEEK calling

ABAP program . The variable is offset in the Bex query designer with a hardcoded -1.

Coz some calculations,filtering, formats which are done at universe or report level could be pushed down to database level (without the need to actually modify the database).

This has advantages, final users have less work to do.

[Former Member]

Hi Srinivasulu,

I know it is possible if we maintain offset in backend BEx query, as we already created one new BEx query and maintaining offset to get one year data.

But now i need the last 5 weeks of data from that given 1 year data. I don't think creating one new Bx query for each this type of requirment is not the best solution.

There is no other solution in webi level to filter last five weeks of data from last 1 year data?? 😞

Any workaround here webi experts please...

Thank you,

[former_member190895]

Hi,

I tried to the solution which i told you and its working fine. PFA screen-shot and below instruction which i did.

1)Max Week=Max([Week]) In Report

2)Last 5 Weeks= =[Max Week]-5

3)Week Flag=If [Week]>[Last 5 Weeks] Then 1 Else 0

Now put the Week Flag filter on crosstab not on report level. This will brings the table to 5 weeks and then remove the week object. Delete the columns. It worked at my end.

[former_member190895]

Apply ranking on the second table for top 3

[Former Member]

Hi Sahu.

I have a requirement like below

If I execute the report for single date it will Execute for 7 days,

Example : If i execute for 22/1/2010 then it will Execute for 7 days, like 22/1/2010to 28/1/2010

please do the needy help sahu.

Regards,

Tulasi

[Former Member]

Hi sahu,

While am doing for  last 5 weeks it showing an error

""last 5 weeks= [max weeks]-5"

[former_member190895]

Create date object at universe level. And use it in the report

Like below:

your_date between @prompt_date between @prompt_Date -7

[former_member190895]

send me the snapshot of variables

[Former Member]

Hi sahu,

we are using Bex, so they dont want to change in bex level we need to do change in webi level

please do the needy

[former_member190895]

Can you write custom sql in the report

[Former Member]

Hi sahu,

Can you help me on that sql query

Please  help me .

Regards,

Tulasi.

[Former Member]

Hi Jothi,

As per your below formula, it will sustract 5 from each week. So it will display again 10 weeks. More over I do not want to add week to table.

(Week([Matweek])-5)

Any way as you mentioned, can you please let me know example formula for if condition

I am mentioning again that expected o/p is: display last 5 weeks of data from given 10 weeks of data for the top 3 materials as below.

Country	Material	Quantity
CountryA	MatC	35
	MatB	30
	MatD	26

Thank you,

suri

[jyothirmayee_s]

Hi,

I'm not sure if you have tried it. It doesn't substract 5 days from the week.

Week([Matweek)-5

it calculated the no of weeks from the given week dimension and display only last 5 weeks.

Post the result after using the formula.

Thanks,

Jothi

[former_member190895]

Remove the week column from the table and see if you gets desired results.

[Former Member]

Hi Sahu,

if we apply flag and then remove the week from the table then it is not giving correct results.

Hi Jothi,

if we add the variable to the table then it is displaying last 5 weeks of data as below.

	Country	Material	Quantity
33.2014	CountryA	MatC	x
		MatB	x
		MatD	x
34.2014	CountryA	MatC	x
		MatB	x
		MatD	x
-----------------	----------------	-----------------	----------------
37.2014	CountryA	MatC	x
		MatB	x
		MatD	x

Can any one help me how to display the last 5 weeks of data from given 10 weeks of data...

Country	Material	Quantity
CountryA	MatC	35
	MatB	30
	MatD	26

if we apply filter on table and select last 5 weeks then data is displaying correctly, but i want filter it to last 5 weeks dynamically...

Thank you,

suri

[harshil_joshi]

Try to hide week column instead of deleting !!!!

[jyothirmayee_s]

Hi,

Create formula @total

=sum([qty])

drag and drop country, material and @total into a table.

Apply ranking on @total to show top 3 or 5 of data.

Thanks,

Jothi

[Former Member]

Hi Jothi,

Thank you.

It will display top 3 materials based total quantity of 10 weeks data but as I mentioned I need it on last 5 weeks of data.

Thank you again,

[jyothirmayee_s]

Hi,

Try the formula @top5weeks

=(Week([Matweek])-5)

and then select the above variable into the table.

Thanks,

Jothi

[former_member190895]

Hi Suri,

Please create a new variable called Max_Week=Max([Week]) = 37.2014

Create another variable Last 5 Weeks = Max_Week- 5 = 33.2014

In your table put the report filter that Week >= Last 5 Weeks.  In this way you have now the table with data for last 5 weeks only. Now you apply the ranking on table.

Let me know if this works.

[Former Member]

Hi Sahu,

As you mentioned, we can get the max week and max-5 as below.

Max_Week=Max([Week]) = 37.2014

Last 5 Weeks = Max_Week- 5 = 33.2014

In filter, we can select only values for week, but as you mentioned how we can select week >= variable (last 5 weeks)?

Thank you,

[former_member190895]

Ok. Create a flag variable then which will give you 1 or 0 value. Then you can filter on 1 on report.

If Week>variable (last 5 weeks) Then 1 else 0

[Former Member]

Hi Sahu,

If we include week in the table then as you said we can try with flag 0 or 1.

But as mentioned I am looking for the below out put without week in my table but data should be restricted to last 5 weeks..

Country	Material	Quantity
CountryA	MatC	35
	MatB	30
	MatD	26

So how we can do it?

[jyothirmayee_s]

Hi,

Did you tried my suggestion?. you don't need to display weeks on the report and still get the result set.

Use If condition

Thanks,

Jothi