How to recreate webi object on business layer?

[former_member342571]

I'm fairly new to BO and universe creation and don't know if my goal is possible or not.

I have created a variable in my webi report that I want to have (recreate) in the business layer of my universe.

The variable calculates the average duration of conversations for where the no. of conversations is not 0.

Webi-Variable:

=If (Sum([Nbr Of Conversations]) >0) Then (Sum([Sum Conversation Duration])/Sum([Nbr Of Conversations]))

I try to recreate it in my business layer to have it available whenever the universe is used. My approach is to just use the formular in the select of an object and change the used objects to the actual columns of the table.

Universe Object:

CASE	WHEN( SUM(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS) >0) 
	THEN ( SUM(CC90_StatisticQueueData.SUM_CONVERSATION_DURATION) / SUM(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS) ) END

I don't receive the same results from the universe object as from the variable in the report. My guess was that it might be a problem with the sum aggregation and deleted the SUM() but the results isn't correct either.

I'm stuck and would be very happy if one of you amazing people would be able to assist.

[amitrathi239]

Nbr Of Conversations and Sum Conversation Duration objects in webi are the dimensions or measure objects?

[former_member342571]

These are dimensions

[Nbr Of Conversations] = CC90_StatisticQueueData.NBR_OF_CONVERSATIONS

[Sum Conversation Duration] = CC90_StatisticQueueData.SUM_CONVERSATION_DURATION

[amitrathi239]

try this.remove the sum from case statement and keep this as dimension object and see.

CASEWHEN((CC90_StatisticQueueData.NBR_OF_CONVERSATIONS)>0)THEN((CC90_StatisticQueueData.SUM_CONVERSATION_DURATION)/(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS))END

[former_member342571]

I already tried this. The thing is if I do it this way, I need to sum it in the report. For the average it is necessary to the summing first and then divide by amount.

Doing it like you suggested will calculate the average for every single line in the table and then sum it which will give faulty values.

[former_member342571]

What I want to achive might be possible with a derived table.

I created a derived table with the following SQL select:

SELECT 
QueueID,
CC90_StatisticQueueData.EntryDate,
I60TimeId,
CASE    WHEN(( sum(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS) )>0) 
        THEN (( sum( CC90_StatisticQueueData.SUM_WAITING_DURATION*86400 ) )/sum( CC90_StatisticQueueData.NBR_OF_CONVERSATIONS )) 
        Else 0
        END AS 'Waiting Duration (Avg)'
FROM
  CC90_StatisticQueueData
GROUP BY QueueID, EntryDate, I60TimeId

It is working but now I'm not able to use the Object "Waiting Duration (Avg)" together with other objects. Are you able to help?

Thx,
Marius

[amitrathi239]

have you tried to create this calculation in Data foundation level?

If not then insert calculated column in the table and insert your calculation.

CASE    WHEN(( sum(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS) )>0) 
        THEN (( sum( CC90_StatisticQueueData.SUM_WAITING_DURATION*86400 ) )/sum( CC90_StatisticQueueData.NBR_OF_CONVERSATIONS )) 

[former_member342571]

This is my table (the original one not a derived table). As you can see I added a calculated column called 'Waiting Duration (Avg)' as suggested.

'Waiting Duration (Avg)':

CASE    WHEN(( sum(CC90_StatisticQueueData.NBR_OF_CONVERSATIONS) )>0) 
        THEN (( sum( CC90_StatisticQueueData.SUM_WAITING_DURATION*86400 ) )/sum( CC90_StatisticQueueData.NBR_OF_CONVERSATIONS )) <br>

When I try to get the tables values it needs to have the other columns in a 'GROUP BY' statement. It says:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'CC90_StatisticQueueData.ContactTypeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

'Waiting Duration (Avg)' needs to have QueueID, EntryDate, I60TimeId grouped or it will not work.

[amitrathi239]

Ok. Then you have already the derived table for the calculation.Join the Derived table with your CC90_StatisticQueueData table on QueueID and create the object in business layer from Derived table and see.

[former_member342571]

I already did that. I have the 'Derived Table' (as explained in the comment above) with the calculated column 'Waiting Duration (Avg)'.

The question is: Am I able to use the calculated column from the derived table together with the objects from the CC90_StatisticQueueData table? If so... HOW?

When I try to use them together e.g. Query Editor -> New Query with:

Queue ID, Entry Date, NBR_OF_CALLS_BEGUN, 'Waiting Duration (Avg)'

It doesn't work. IDT gets stuck and I have to cancle the query.