Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

claudialet
New Contributor III

How to exclude certain row from being calculated ?

 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.

 

8 Replies

Re: How to exclude certain row from being calculated ?

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 ?

Or this

Sum({$+1<[Line Des] = {'Orange'}>}Amount)
Highlighted
claudialet
New Contributor III

Re: How to exclude certain row from being calculated ?

 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 ?

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?
claudialet
New Contributor III

Re: How to exclude certain row from being calculated ?

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 ?

What exactly is Level here?

claudialet
New Contributor III

Re: How to exclude certain row from being calculated ?

 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 ?

All I see is this

image.png

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