- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Dynamic Average Calculation

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Dynamic Average Calculation

Former Member

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 01-04-2008 10:50 PM

Hi,

I have a requirement to calculate average dynamically. I need to calculate the average number of service tickets per 'product line'.

so report shud look like as follows.

Number of tickets Average Tickets

PL1 20 15

PL2 10 15

PL3 15 15

Total 45 15

Now say a drilldown is added with employees and the reprot looks as follows:

Number of tickets Average Tickets

PL1 David 18 10

PL1 John 2 10

Total 20 10

PL2 Rita 7 5

PL2 Jay 3 5

Total 10 5

and so on......

The average calcualtion depends on the characteristic in the rows and the number of values each drilldown combination has. As a result i have not been able to use Exception aggregation. I also do not see 'Average' available in the calculations tab for the KF/Formula properties (there is only weighted average). I am using BI 7.0

I tried to create a dummy KF Count with '1' in the formula and then use the calculation SUMCT 'total tickets'/ SUMCT 'Count'. But it does not seem to be working for 'Count'.

Let me know what I am doing wrong or if there is any other way.

Thanks

Gaurav

- SAP Managed Tags:
- BW (SAP Business Warehouse)

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-06-2008
8:36 PM

Hi Olivier,

The requirement is as u mentioaned...the Average Basket value for any combination of free characteristics....

The ides is that we need to be able to compare the number of tickets reolved/handled by an employee compared to the average for the respective product line, Service team group etc.

I am unable to use the total of the Count (KF with 1) while trying to do the calculation using SUMCT. The total comes out as 1 instead of the total number of rows.

Gaurav

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-06-2008
8:31 PM

Hi Vishno,

Thanks for your response. I already have the steps 1 to 5 implemented and this the solution I had been looking for. But the issue is with step 5 - ii. I do not see the option of average in calculation in BI 7.0. I see only 'Moving average of all values.'

Is that an issue with the version of my BEx?

regards

Gaurav

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-07-2008
2:55 AM

This might be caused by the BeX version.

I am on SP 0 & Revision 418.

"Avg of all values" and "Average of all values <>0" is what is showing up.

This happens for both "Calculate single values as" & "Calculate result row as".

Also I think if you follow all of the steps then using "Moving Average" should also work.

That's what I tried to mean in my 2nd post i.e. this average is not static but is moving/dynamic depending on the characteristics.

Hope it helps.

Assign points if it helps.

Thanks

Vishno.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-05-2008
12:00 AM

1. I assume the Dummy KF with a formula '1' was created at

the query level. This will not give the desired output.

2. Add a KF ZCOUNTER to the data target. Populate it

through transformation or Update/Transfer rule. Set the

formula as constant '1'.

3. Now in the Bex, create a global Calculated KF, ZCOUNT *

(not local formula)* .

Drag & Add ZCOUNTER to ZCOUNT.

Now from the Properties of ZCOUNT, set the Exception

Aggregation as "Count" and Refernce Char as "0TICKET"

(assuming Service Tkt No is mapped to 0TICKET in the

Transformation)

4. This new CKF, ZCOUNT will be used in the query.

Drag it into the "Columns" and set it to "Hide" from

*Properties*.

5. In the display create two Formulas, "No of Tickets" & "Avg

Tickets".

i) Drag ZCOUNT directly into "No of Tkts" formula.

No other setting needed.

ii) Drag ZCOUNT into "Avg Tickets". From the

*Calculations tab in the Properties* set both

the "Calculate Result as" & "Calculate Single Values

as" to "Avg of all values".

iii) Make sure to display the Results Rows of Employee,

Product Line or whatever caharacteristic needed. Else it

will not show.

Hope this helps.

Thanks

Vishno.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-05-2008
12:29 AM

Hi,

the solution provided is good but

- why do you need to add one keyfig as 1 in the provider? counting any keyfigure with the same exception on Ticket will produce the same.

- I don't think it will match the requirement since it is needed to show the next subtotal for all the records belonging to it.

First I would like to know the final requirement because what you describe might match the final requirement like the Average Basket value for any combination of free characteristics....

then regarding the problem of the SUMCT (No of tickets) / SUMCT (count) I am not sure yet if this can be overcome... would need more time...

Olivier.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-05-2008
2:04 AM

1. It's true that any KF should work but for counting purposes it's suggested to maintain a separate KF. Please refer to the document below (Steps1-4).

[How to...Count|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/7e58e690-0201-0010-fd85-a2f29a41c7af]

2. I think the property *"Calculation Results rows as"* is dynamic i.e. the value calculated according to this property setting will be determined from what actually "the Result Row" is.

Hence, the Average displayed in the Employee Result row will be different from the Product Line Result row, which is desired.

Please suggest better options/alternatives that you think.

Thanks

Vishno

Ask a Question

Related Content

- Global Explanation Capabilities in SAP HANA Machine Learning in Technology Blogs by SAP
- Moving Average Trend calculation in Technology Q&A
- Combining measures with dimensions in table columns + dynamic headers (SAC) in Technology Q&A
- BENCHMARKING FUJITSU’S DIGITAL ANNEALER TO SOLVE COMPLEX COMBINATORIAL OPTIMIZATION PROBLEMS in Technology Blogs by SAP
- What’s New in SAP Analytics Cloud Q1 2024 in Technology Blogs by SAP

Top Q&A Solution Author

User | Count |
---|---|

64 | |

25 | |

10 | |

9 | |

7 | |

5 | |

5 | |

4 | |

4 | |

4 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.