cancel
Showing results for 
Search instead for 
Did you mean: 

Calculations using calculated data in adjacent columns with different context

0 Kudos

I have some data in a universe that I need to do some strange aggregations on in a web intelligence report. I'm stuck on the second and third aggregations after using a cross table to get the first aggregation. Hopefully this isn't a duplicate question, apologies if so; I've googled for hours.

Here's the general process I need to do:

  1. I need to find the sum of all [Hours] for each [Part] in each [Machine] in each [MonthYear] and this is the vaue that gets put in the [Month Sum] column (let's call this step 1)
  2. I need to take the mean of (step 1) for each [Part] in each [Machine] (i.e. average of each [sum of all operating hours for each part in each machine in each month-year] for each part in each machine). This gets put in the [Average of Month Sums] column (let's call this step 2)
  3. Lastly I need to do the same thing as (step 2), just this time the sample standard deviation instead of average (shouldn't be an issue if I can get step 2 to work, just copy+paste and change the aggregation function). (step 3)

I have some sample data and I've gone through the steps as best I could in Excel. I can post .csv sample data as well if needed. I'm not sure how comprehensive this dummy data is, I'm unable to post real data but I believe it is sufficient.

I'm able to get (step 1) working with a cross table that I then convert to a vertical table. But when I try to do (step 2) by inserting a column in the vertical table using something like

=Average([Month Sum]) In ([Machine]; [Part])

it doesn't give the correct values. I've gone through the real report and checked the values on my calculator by showing a subset via the filter bar and I've double checked the data by downloading the .csv report and running it through a script I wrote in another language.

The excel sheet in the image is formatted to be easy-ish to read. I don't need the actual report to turn out that way, I just need a way to get the aggregation steps done in SAP Web Intel with correct values. It can use multiple reports if needed but the fewer reports the better. In the end the reports will be downloaded as .csv or .txt files.


The [Machine Part] column doesn't really exist in the report or data, I just put it in the excel sheet to give some more detail (Machine A has parts a and b; A.a != B.a; etc.). You can completely ignore it though if it is confusing.

Sample Data and general process I need to do:

Excel w/ formulas shown:

0 Kudos

Edit 1:

Minor error in the Machine entry in fig0.png. Have fixed it and the corrected image is included here. I'm not sure it will let me edit the original post.

Accepted Solutions (1)

Accepted Solutions (1)

ayman_salem
Active Contributor

here the solution:

additional to your Variables add followings;

Month: =MonthNumberOfYear([Date])

Average of Month Sums: =Average([Month Sum]) In ([Machine];[Part]; [Month])

Std Month Sum: =StdDev([Month Sum]) In ([Machine];[Part];[Month])

(updated)

..

I hope it helps you complete your report.

...

note:

It is not necessary to define additional variables "A" and "B" by defining the variable "Machine"

The variable "Machine" could look like this: =Right(Trim([Model]);1)

ayman_salem
Active Contributor
0 Kudos

Screenshot is updated (with correct data)

0 Kudos

I think this is going to work for me! Thanks a bunch!!

Answers (1)

Answers (1)

ayman_salem
Active Contributor

Something is wrong with the input data in your Excel spreadsheet. Part "36A" is identical to "18A" in the date (1.1.2017). but you put it with machine "B". I think it should be "36B"

Can you confirm it?

0 Kudos

You are correct; good catch. It doesn't really make a difference in what I need to do, but I can see how it could be confusing. I have corrected it and commeted on my original post with a corrected image (fig0_mod1.png)

lenastodal
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Andrew,

I wanted to give you a response to your question regarding the Edit rights to a question you asked. Generally, you can revise your question by selecting Actions, then Edit. However, once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment as you correctly did.
Many thanks and keep it up!

Lena
SAP Community Moderator