topic Re: Set Analysis in QlikView

Set Analysis

bvann040684 — Tue, 10 May 2011 15:56:57 GMT

I'm having some trouble setting up my set analysis expression.

I need to sum the Premium where there is and is not commission. The two expressions I'm using are:

Premium with Commissiohn ~ Sum ({$<[Invoice Line No] = {"= [Insurer Earn Commission] <> 0"}>} Premium * InvLineExchRate)

Premium without Comm. ~ Sum ({$<[Invoice Line No] = {"= [Insurer Earn Commission] = 0"}>} Premium * InvLineExchRate)

These work when a specific invoice has been selected but its rolled up into Office or Region all i get is zeros. If i change '[Invoice Line No]' to '[Invoice No]' it doesnt look at the individual lines anymore and just does a sum of the Invoice instead of each line. Since there can be muliple lines on each invoice each with or without commission I get the wrong numbers

Can anyone help me?

Inv Line #	Premium	Comm	Prem w/ Comm	Prem w/o Comm

1	10	5	10
2	20			20
3	30	5	30
4	40			40
5	50	5	50
Total	150	15	90	60

Re: Set Analysis

Tue, 10 May 2011 16:20:02 GMT

If your filter you want to add is based on the "commission" status, why do you use the "invoice line no" dimension in your Set Analysis ?

Sum of "Premium" having commission :

[code]

=sum({$ <[Insurer Earn Commission]={'<>0'}>} Premium)

[/code]

Sum of "Premium" having no commission :

[code]

=sum({$ <[Insurer Earn Commission]={'0'}>} Premium)

[/code]

Set Analysis

Tue, 10 May 2011 20:54:31 GMT

Well first of all I don't really understand your set analysis as I think it should be what nmartin above said (except I would use -={0} instead but I don't think it matters).

You say it works when you select one invoice but that is the only time it works which makes me think your problem is that set analysis is calculated once per chart, not once per row. I don't really understand your data so I don't know for sure that is probably the problem. You probably need to use the aggr function which often isn't the most fun thing in the world.

Set Analysis

bvann040684 — Wed, 11 May 2011 14:34:43 GMT

Neither answer worked.

I originally added Invoice No and Invoice Line No because the Commission was by line in each invoice so I needed a line by line answer.

I updated the orinigal question with a chart that might explain it better.

Re: Set Analysis

Wed, 11 May 2011 15:10:24 GMT

Bvann040684 a écrit:

Neither answer worked.

I've tested with what I said earlier, just replacing the "0" by a empty string value when there is no Comm (I thought "no comm" was "comm = 0"):

[code]

LOAD * INLINE [

Inv Line #, Premium, Comm

1, 10, 5

2, 20,

3, 30, 5

4, 40,

5, 50, 5

];

[/code]

Expressions:

[code]

=sum({$ <[Comm]={'<>0'}>} Premium)

=sum({$ <[Comm]-={''}>} Premium)

[/code]

and

[code]

=sum({$ <[Comm]={''}>} Premium)

[/code]

And I have the following result:

Is you don't want to use set analysis, you can use simple conditions :

[code]

=sum(if([Comm] <> '', Premium))

and

=sum(if([Comm] = '', Premium))

[/code]