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
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.
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
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