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,
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
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
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
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
Hi Noel,
Please close this question by awarding "best answer" and/or closing it via the menu under your avatar.
Regards,
Johan