Dynamic Average Calculation

[Former Member]

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

[Former Member]

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]

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]

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]

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]

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]

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