simple Set Analysis

[#1]

Hello Experts,

I have a simple query, just that I am unable to get my head around with this.. please help me out with this.

=if(Deal_Stage='DIPs',num(sum(Value* WA_LTV)) / sum(if(WA_LTV <= 0,0,Value)))

I want to write this using set analysis

Thanks in Advance

[#2]

???? Anybody?

[Miguel_Angel_Baeyens]

Hello,

Try the following

=sum({< Deal_Stage = {'DIPs'} >} Value * WA_LTV) / sum({< WA_LTV = {"<=0"} >} Value)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hello Miguel,

Thanks for the quick response, but the numbers between the 2 expressions, the oriinal IF Query and the one using Set Analysis doesnt match. I also tried storing this caluclation in a Variable and using it.. do you have any more ideas please??

=if(Deal_Stage='DIPs',num(sum(Value* WA_LTV)) / sum(if(WA_LTV <= 0,0,Value)))

=sum({< Deal_Stage = {'DIPs'} >} Value * WA_LTV) / sum({< WA_LTV = {"<=0"} >} Value)


There is a difference in the result between these above 2 expressions.

Thanks in advance

[Miguel_Angel_Baeyens]

Hi,

They usually return different results when using If() than when using set analysis. The set analysis is calculated once for the whole chart, and the If() is evaluated for each row (for each value of the dimension). This is one of the reasons why set analysis is faster than if(). Anyway sometimes there's no other way to get the results you expect but using if()'s.

In my expression above I converted the if() to a set analysis, but I made a mistake in the modifier

=sum({< Deal_Stage = {'DIPs'} >} Value * WA_LTV) / sum({< WA_LTV = {">0"} >} Value)

That should be more similar to what you expect (the WA_LTV in the second Sum() is set to greater than zero, as in your conditional).

Post some sample data and the expected results and the type of object you are using (pivot table, straight table) so we can check further if the above doesn't help.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Hello Miguel,

For now this looks like a temporary solution for me,.. wish I could have sent some sample data, but cannot as its confidential. Anyways I will get back to you for sure if i need more info.

Thanks Again for your time and effort.