11 Replies Latest reply: Jul 26, 2015 11:13 PM by jagan mohan rao appala

sum + case

hi,

I would make the sum of two fields conditioned by another value
example :
case white

sum(1+10):

case red

sum(2+20)

else

sum(3+20)

• Re: sum + case

You would use an IF statement for this in Qlik.

Either on load to create a new value or in the measure as an expression assuming white and red are dimension values.

.

.

If([Field] = White, sum(1+10), If([Field] = Red, sum(2+20), sum(3+30), 0))) As New Field

• Re: sum + case

You can also combine WildMatch and Pick:

Pick( WildMatch( Color , 'white' , 'red' , '*' )

Sum( 1 + 10 )

, Sum( 2 + 20 )

, Sum( 3 + 20 )

)

• Re: sum + case

Hi,

If you have many Cases then best approach is to use Pick() and WildMatch() like below

Pick(WildMatch(FieldName, 'white', 'red', '*'),

Sum(1+10),

Sum(2+20),

Sum(3+20))

Hope this helps you.

Regards,

Jagan.

• Re: sum + case

hi,

could I apply this formula at a total?

• Re: sum + case

Hi,

Where you want to get the total?  If it is straight table select

Properties-> Expression-> Sum of Rows

Hope this helps you.

Regards,

Jagan.

• Re: sum + case

It is better not to use IF statement for better performance. IF will consume more resources.

Thank you.

• Re: sum + case

hi,

excuse me, It wasn't clear.

I need.

sum everything except field = VALUE

• Re: sum + case

May be try

If([Field] = 'Value',0,if([Field] = 'White', sum(1+10), If([Field] = 'Red', sum(2+20), sum(3+30)))

• Re: sum + case

Hi,

Try like this

Pick(WildMatch(FieldName, 'white', 'red', '*'),

Sum({<field -= {'VALUE'}>}),

Sum({<field -= {'VALUE'}>}),

Sum({<field -= {'VALUE'}>}))

Qlikview compiler will show the above expression as error but it works, because -= it will show error.

Hope this helps you.

Regards,

Jagan.

• Re: sum + case

Hi,