Skip to Content

How to recreate webi object on business layer?

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 11, 2017 at 02:34 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

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