To do calculation in column according to selection in List Box

[#1]

Hi Everybody

I am Deepak, I am new to Qlikview,

So i need some help in one task

I have List Box, Intra-segment costs with 2 rows Keep Cost, Flow Cost that look like as follow

Intra-segment costs

Keep Cost,

Flow Cost

I have a column in load statement as ([Net Costs M-Rate] - ( if(isnull([Intra Segment SGA M-Rate]),0,[Intra Segment SGA M-Rate]) )

and this columns is used in many charts as expression

I want that in front End , if user select Keep Cost, in list box the column should be as it is [Net Costs (x intra SGA) M-Rate] or [Net Costs (x intra SGA) A-Rate]

If user select Flow Cost in list box then column should be multiplied by 0 or Zero like

[Net Costs M-Rate] - ( if(isnull([Intra Segment SGA M-Rate]),0,[Intra Segment SGA M-Rate]) * 0 )

and accordingly all chart will get updated where this Column is used as expression

It would be very helpful if anyone let me know how to do it

Thanks

Regards

Deepak Bajaj

[Oleg_Troyansky]

Deepak,

first of all, there is a better way to avoid nulls in your expression. Instead of using if(isnull()..., you can simply use function RangeSum and that will eliminate any nulls(), replacing them by zeros, so you formula would look like this:

RangeSum( [Net Costs M-Rate] , -1 * [Intra Segment SGA M-Rate])

In your run-time chart expression, you can simply check the value of your list box field and determine what formula to use:

if( only([Intra-segment costs]) = 'Keep Cost', sum( .... first formula ... ) , sum( ... second formula ... ) )

good luck!