Resolving error in calculated member : The '/' operator cannot be used with sets

Question

Hello,

I'm trying to create a calculated member that devides the result of a slice operation containing a set by the result of another slice operation, however I receive an error "The '/' operator cannot be used with sets".
Does anyone know how to rewrite the query or in another way resolve this?

My code:

create member currentcube.[measures].[Dealer effort Office Hours]
 as case
// test to avoid division by zero.
when isempty
     (
       [Measures].[Expenditure count]
     )
then null

else
     (
       {[v Dim File Exp].[Benefit].&[1e Towing],[v Dim File Exp].[Benefit].&[2e Towing]},
       [v Dim File Exp].[Supplier Category].&[CAR DEALER],
       [Exp Insert Time].[Time].[Day Part].&[4],
       [Measures].[Expenditure count]
     ) 
   /
     (   {[v Dim File Exp].[Benefit].&[1e Towing],[v Dim File Exp].[Benefit].&[2e Towing]},
        [v Dim File Exp].[Supplier Category].&[CAR DEALER],
        [Measures].[Expenditure count]
     )
    

end ,
format_string = "percent",
visible = 1  ;

So what I'm trying to do is defide all the occasions a CAR DEALER does a 1e Towing or 2e Towing during office hours by
all the 1e Towing and 2e Towing done by CAR DEALER

Thanks in advance for your help,

Edo

Answer 1

Hi Edo,

you should use an aggregation function, like Sum for example, when dealing with sets and measures.

See here:
 

else

Sum(

{ [v Dim File Exp].[Benefit].&[1e Towing], [v Dim File Exp].[Benefit].&[2e Towing] } *

{ [v Dim File Exp].[Supplier Category].&[CAR DEALER] } *

{ [Exp Insert Time].[Time].[Day Part].&[4] },

[Measures].[Expenditure count]

)

/

Sum(

{ [v Dim File Exp].[Benefit].&[1e Towing], [v Dim File Exp].[Benefit].&[2e Towing] } *

{ [v Dim File Exp].[Supplier Category].&[CAR DEALER] },

[Measures].[Expenditure count]

)

Hope it helps,


Tomislav Piasevoli
Business Intelligence Specialist
www.softpro.hr

Answer 2

Hi Tomislav,

That works, thank you very much.

Edo.