topic simple Set Analysis in QlikView

simple Set Analysis

Mon, 20 Jun 2011 08:33:45 GMT

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

simple Set Analysis

Mon, 20 Jun 2011 08:46:28 GMT

???? Anybody?

Re: simple Set Analysis

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 08:48:16 GMT

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

simple Set Analysis

Mon, 20 Jun 2011 09:00:56 GMT

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

Re: simple Set Analysis

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 09:16:41 GMT

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

simple Set Analysis

Mon, 20 Jun 2011 10:40:11 GMT

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.