Copy a variable from one report tab to another report tab

[former_member731723]

I created a variable in one report tab which contains the average of several columns for each row. I tried to drag this variable into a new column of another report tab. However, the values do not match.

What I wish to do is equivalent in excel to copy one column from one worksheet that contains a formula and paste this column as a number in another worksheet.

I tried to reference a cell but it does not work. It copies only the first value as a constant to the whole column.

App: SAP Business Objects - Web Intelligence Rich Client

EDIT: include pictures of data sample

So, in the first report tab I have a cross-table. The first column is a dimension that represents the ID of the products, the second column is a variable that constains the average of all values across the different weeks for each product ID.

What I wish is to copy this AVG variable that is located in the Average_created tab to the report in the Average_copied tab.

However, when I copy this variable, I obtain a different result as you can see in the picture below.

[KatiNonhebel]

Welcome and thanks for visiting SAP Community to get answers to your questions. Check out our tutorial to get started in SAP Community: https://developers.sap.com/tutorials/community-start.html By adding a picture to your profile you encourage readers to respond: https://www.youtube.com/watch?v=46bt1juWUUM

[ayman_salem]

To give you a possible correct answer, a screenshot with sample data and the expected result is helpful

[ayman_salem · Accepted solution]

Define your average variable as follows:

AVG: =Average([V] ForEach([Week]) )

...

hopefully that helps

[former_member731723 · Accepted solution]

Thank you for the help ayman.salem! I can clearly see that in your example it works. But unfortunately in mine it does not. When I update the formula for what you suggested (Average([var_smape_modif] ForEach([dim_ISO_ohne_W]) )), my result in the second tab changes (average_copied), but it still does not remain as what it should be, as you can compare with the photo I published in my question under the average_created tab.

I am not sure what is the problem with my data structure and variables creation. So, I wil add a few remarks:

- I need to merge different weeks to be able to create this report. There are weeks where I have the data in yyyyww structure and there are weeks where I have the data in yyyyWww structure. So I also create a new variable without the W, which is used here in the cross table for a better read.

- the average is the mean of an error measure. So, first I take the actual values for week 10 and compare with the predicted values for week 10 and create an error measure.

- there are some empty cells which I decided to fulfill with a 0 (=If(IsNumber([var_smape]);[var_smape];0))

- In the end, I evaluate the average error for that particular material inside a time period. This is the variable I want to copy to the second tab. This is what I call AVG.

[ayman_salem · Accepted solution]

is week came from two or more data providers?

Did you manually check which average is correct (Average_created or Average_copied)?

Is there a filter on the Average_created tab? or on the table?

Also check the table on the "Average_copied" tab for duplicate "Identnummer" (just sort the table by "Identnummer" only and determine whether or not there is a duplicate value).

...

By the way, you can include empty cells in the average calculation

=Average([var_smape] ForEach([dim_ISO_ohne_W]); IncludeEmpty)

[former_member731723 · Accepted solution]

yes, it comes from 2 data providers

Average_create is the corrected value. I did check manually

For me, it worked when I copied all the weeks again from the cross table to my report tab, did the calculations, and then hidded these columns. By doing that, the value did not alter and when the data is exported to excel it does not show the columns I do not need.

I don't think this is the best solution, but it worked.

Thanks a lot for your help!

[ayman_salem · Accepted solution]

The problem seems to be with how the 2 data provider merges which I can't solve as I don't have any sample data from the 2 data provider. So try to investigate the merge.

....

If you found my answer ok, please accept it and close the question

[former_member731723 · Accepted solution]

Thanks a lot for all the help and explanation. I already accepted your answer but let me know if it did not work, it shows as green to me and labeled as "Best Answer"

[former_member907524]

Hello,

Under the analyze tab, click the little arrow beside the Options. Click show report filter pages. Click Month and then click OK. You're Done! if ever that you entered more data on your orders table, just click Refresh All under the Data tab and everything will be updated. I have attached the file which includes the sheets for Sep and Oct.

[former_member731723]

Hello abigali . Unfortunatelly, I can not find the arrow besides the Options that you mentioned. I am sorry. I am new working with this tool. I added a photo to the question, can you guide me? Thank you very much!