topic Re: How to exclude certain row from being calculated ? in QlikView

How to exclude certain row from being calculated ?

claudialet — Fri, 11 Jan 2019 12:29:15 GMT

Here is attached data and view of table

Entire table is being calculated by : sum(amounts * rate)

However do I make a certain row not be affected this expression and just display value as is ?

Entire table is calculated. One row should just show the amount as is.

Re: How to exclude certain row from being calculated ?

sunny_talwar — Fri, 11 Jan 2019 12:35:09 GMT

So, in your example, you want Orange to always show the same number regardless of the selection, but everything else should change? May be this

Sum({<[Line Des] += {'Orange'}>}Amount * Rate)

Re: How to exclude certain row from being calculated ?

sunny_talwar — Fri, 11 Jan 2019 12:38:56 GMT

Or this

Sum({$+1<[Line Des] = {'Orange'}>}Amount)

Re: How to exclude certain row from being calculated ?

claudialet — Fri, 11 Jan 2019 15:30:15 GMT

Sorry that is not it.

I have a complicated roll up by level filter requirement. It should be

Filter Level 1 : sum ( Amount * Rate) for the whole table except row 'Orange' ( display as is)

Filter Level 2 : sum ( Amount * Rate) for (Dim1 + Dim 2) but because of auto sum function at level 2 row 'Orange' adds no matter what.

Any ideas ?

Re: How to exclude certain row from being calculated ?

sunny_talwar — Fri, 11 Jan 2019 16:17:56 GMT

I am not sure I follow... would you be able to provide the expected output based on the sample data you have in the image?

Re: How to exclude certain row from being calculated ?

claudialet — Fri, 11 Jan 2019 16:54:00 GMT

Here is my expression. It seems ok at Level 1 but Level 2 is not right. It auto sums row ‘Orange’
And data at level2 does not flow in correctly.

sum(distinct(ValueAmount) * if($(vFilter)= ' Level1', Percent,1) *if($(vFilter)= ' Level2', FXRate,1))

At Level 1 : Show sum (amount * Percent)
At Level2 : Show sum ( amount * Percent * FXRate)

But at both levels row ‘Orange’ should be just display ( Amount) as is calculation has no effect.

Re: How to exclude certain row from being calculated ?

sunny_talwar — Fri, 11 Jan 2019 17:11:42 GMT

What exactly is Level here?

Re: How to exclude certain row from being calculated ?

claudialet — Fri, 11 Jan 2019 17:16:21 GMT

It 's a group filter extension and levels are assigned by variables. Vfilter =1 and so on

So Level1 - has a entity dimension and level2 has another dimension that rolls up into Level 1

Re: How to exclude certain row from being calculated ?

sunny_talwar — Fri, 11 Jan 2019 17:20:33 GMT

All I see is this

And I see reference of entity or any other dimension. I am not sure I can follow your requirement. Unless you are able to provide a sample where we can see all of this, I am not sure how to help you.

Best,
Sunny