How to incorporate Median Formula in SAP Formatted Search or Query Generator

[Former Member]

Hello there SAP community,

I'm quite having a problem on how to incorporate the Median formula in a Formatted Search or Query Generator; Can anyone please help me by sharing some simple codes so i can have an idea on how to ??? 🙂

Thank you in advance

Kind regards

Noel,

[Johan_H · Accepted solution]

Hi Noel,

A query can contain pretty much any formula. A very simple example:

SELECT T0.ItemCode
      ,((T0.Price * T0.Quantity)-(T0.GrossBuyPr * T0.Quantity)) / (T0.Price * T0.Quantity) AS [Gross Profit]
FROM INV1 T0
     INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.DocNum = [%0]

If you know the formula you just have to fill in the variables (fields)

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

Very much appreciate your reply ( with given formula ). But would you know how to apply a Median formula in query generator like in excel "=MEDIAN(1,2,5,6)" <---- [ this is the exact formula in excel ] .

( if Median has been tried in SAP Query generator, would kindly like to know how it was done )

Thanks in advance 🙂

Noel

[Johan_H · Accepted solution]

Hi Noel,

To my knowledge there is no ready function in MS SQL that compares to Excel's MEDIAN function.

Now I have not done this myself yet, but here are two sql implementations of the median formula in SQL. I recommend that you read the entire article yourself, but the gist of it is that the first version is the best. You may be able to copy paste the query, and edit it to use your B1 data.

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan,

This is an eye opening advice regarding to the MS SQL and Excel's Median Function.

However, I've read the link you provided and that is enough on how to start with testing the query.

Thank you for this one,

Really appreciate it 🙂

Noel

[Johan_H · Accepted solution]

Hi Noel,

Please close this question by awarding "best answer" and/or closing it via the menu under your avatar.

Regards,

Johan