Skip to Content
author's profile photo Former Member
Former Member

Dynamic Average Calculation

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 05, 2008 at 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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2008 at 08: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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2008 at 08: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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.