Creating Key Figures – Data Operators
This blog will approach Data Operators for creating new key figures.
If you want to check another available operators, please check the blog posts below:
- Boolean Operators: https://blogs.sap.com/2019/04/12/creating-key-figures-boolean-operators/#
- Mathematical Operators: https://blogs.sap.com/2019/04/17/creating-key-figures-mathematical-operators/
- Trigonometric Operators: https://blogs.sap.com/2019/04/24/creating-key-figures-trigonometric-operators/
Data Operators in your system
I’m going to provide with a practical example on the operators. Please consider the formulas used and change their values as below:
ValueOrKeyFigureToBeCompared1 = Confirmed Quantity
ValueOrKeyFigureToBeCompared2 = Ordered Quantity
After the explanation of each Data Operator, you will find a table with example of possible results based on the provided values and formulas.
It counts the number of values per row.
Is zero if the calculation causes a division by zero. Otherwise, the regular result of the expression will be provided.
Example: NDV0(“ValueOrKeyFigure1”/ “ValueOrKeyFigure2”)
Is zero if the calculation of the mathematical expression leads to an arithmetical error.
Example: NOERR(“ValueOrKeyFigure1”/ “ValueOrKeyFigure2”)
Indicates how high the percentage amount is with regard to the result. Result here means the result of the aggregation at the next-highest level (intermediate result).
Indicates what percentage the amount is of the grand total. Grand total here means the result of the aggregation at the highest level of the list.
Transforms a value into date.
Returns value 1 if the <expression> is 0, otherwise 0
Example: DELTA(“ValueOrKeyFigure1”/ “ValueOrKeyFigure2”)
It removes any unit or currency existent in the key figure value.
Returns the result of the operand to all rows or columns.
Switches the value of a key figure to time.
Table with examples for Data Operators
I hope you all have been able to understand a bit more on Data Operators. If you have any doubt, please do not hesitate on commenting them below. ?
Do you think we can create a fixed line running across bar charts? For example, I have a bar chart representing the number of opportunities per month for current year. Let's say the sales reps have a target of 10 opportunities per month. No, on my chart, I want to show this value 10 as a line. Think it is possible?
I tried creating a calculated key figure: Number of Opportunties EQ 10 but the result remains 0.
I hope you are doing well.
Currently, SAP Hybris Cloud for Customer does not support target line in our charts/reports.
However, I have a workaround that might help you to achieve your business needs. In order to create the needed key figure (variable), please follow the steps below:
Now the created key figure will show only 10 independently on your other key figures value.
I have added this key figure to a bar chart within Opportunity Pipeline Report CODOPPU_Q0014, and it is being shown like this:
As I don’t believe that this chart is user friendly, I have tried changing it to Line chart type as below:
Please let me know if does it fit to your business needs. Also, I would like to kindly ask you to address an idea for standard target line into our SAP Customer Influences, as I strongly believe that this is an excellent suggestion. If you decide to do it so, please inform me the idea URL and I will be more than happy on voting for it as well.
Hi Elise! 🙂
Thanks a looot! That is indeed what I was trying to do except that instead of just adding the number on the formula label, I was trying an actual formula!
I am actually using it on a dual axis combination chart and so far works well. This is what I was trying to do:
I noticed though that on this type of chart, I cannot add a "Target" as you have shown based on the Y-axis (items won + lost on the screenshot). There was a final line I would have loved to add that is set at let's say 10M. If I proceed using the same logic as above, the whole graph gets messed up as I believe the result is being based on the Percentage (%) axis. I wanted to add another line showing for example 20M (straight line).
I have shared the idea:
Thanks again Elise, great help as always 🙂
Hi Naseef 😀
Thanks for your feedback, I'm glad you achieved your goals on it.
I have just voted on the submitted idea and asked my colleagues to do the same.
Have a nice day ahead.
How do we convert a value to a Date? Whats teh format of the 'value' that is converted by a DATE To a constant value of MM-DD-YYYY or an acceptable format that can be used in Key figures as a date?
Hi Elise Teixeira,
Thank you for the very informative blog...! this is very helpful.
We have a below requirement in our business process,
We want to compare the Actual sales and Targets Amount in C4C. The primary key is Account+ Product Category+ Year+ Month. Actual Sales values are replicated from the BW system in to a Cloud datasource (CDS) and the Target Amount is maintained in Sales Target Planning.
Consider we do Left outer Join between these Datasource with Actual sales CDS as Anchor. In Actual sales CDS the entry for the Combination "Account 1 + Product Category 1 + 2020 + January" is not there, but the Target Amount maintained for this Combination in Sales Target planning. But in the Joined data source we will lose this row as the leading data source is not having this combination.
But as per our requirement, all the combinations of "Account+ Product Category+ Year+ Month" should available in the target report irrespective of this entry is available in Actual Sales CDS (or) Sales Target Planning datasource.
I tried with the combined datasource as well, but here we get the entries with # and i think this is limitation explained by SAP in the KBA
Currently we are stuck here, Please suggest if we have some option to satisfy this requirement.
Thank you in Advance..!
In ByD I am looking to have an If Statement like in Excel, but I can't for the life of me figure out how to do this.
I have a restricted key figure, Base Qty, that looks at a certain product (base licenses) and multiplies it by 5
then there is the other base key figure requested quantity
I want the new key figure to if (Base > requested, base, requested)
I know that I can do this with a series of restricted and calculated key figures but this is sooo cumbersome. There has to be a better way!
Hi Elise Teixeira,
I have a key figure where in I'm confused of which operators I need to use so as to get the values in desired format/result.
Let's say I have two values VALUE 1 & VALUE 2 for which I will divide and I need the value to be in percentage instead of in decimals.
e.g.: VALUE 1= 1 & VALUE 2 = 5
1/5 is 0.2 which is in decimal format but I need the result value to be in percentage, so the desired answer will be 20%.
Kindly help me in knowing of which operators I can use for the above point.
Hi Elise Teixeira,
We are trying to show a key figure in hh:mm:ss but are having an issue where the time period is more than 24 hours, as the TIME operator only considers less than 1 day and strips off any further time e.g. if we have a value of 1 day, 5 hours, 3 minutes, 17 seconds we would want it to show as 29:03:17. Is there a way to achieve this aim that you can think of without having to split across multiple key figures (which causes other problems)? The issue is documented here https://userapps.support.sap.com/sap/support/knowledge/en/2635002 but the solutions do not work for what we are trying to achieve.