Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rglenn0780
Contributor

Exclude a product line from measure equation

Hello and Happy New Year!!!

I am attempting to exclude a certain product line in a capital KPI chart.

The measure is: sum([capital])

So I attempted with this formula.

If([Business Segment]='Fruits', not Match([Product Line],
'Grapes'), [Product Line]

and

sum([Capital]))

What I am trying to do is say, when I am selected on Fruits, I want to exclude grapes (is a product line) from the KPI capital number.

Currently, I am getting a dash with the above equation.

Thanks in advance for your help!!!!

Tags (1)
1 Solution

Accepted Solutions
rglenn0780
Contributor

Re: Exclude a product line from measure equation

Sunny,

You are the best. A little clean up and you are correct

If([Business Segment]='Fruits', Sum({<[Product Line] -= {'Grapes'}>}[Capital]), Sum([Capital]))

I removed the getfieldselections due to a nested agg error.

Now I need to go the other way and add a product line to a segment. instead of using -=, is there something that adds a product line in?

13 Replies

Re: Exclude a product line from measure equation

May be this

If(GetFieldSelection([Business Segment] = 'Fruits', Sum({<[Product Line] -= {'Grapes'}>}[capital]), Sum(capital))

rglenn0780
Contributor

Re: Exclude a product line from measure equation

Sunny,

You are the best. A little clean up and you are correct

If([Business Segment]='Fruits', Sum({<[Product Line] -= {'Grapes'}>}[Capital]), Sum([Capital]))

I removed the getfieldselections due to a nested agg error.

Now I need to go the other way and add a product line to a segment. instead of using -=, is there something that adds a product line in?

rglenn0780
Contributor

Re: Exclude a product line from measure equation

Do I mark yours as correct since it lead to the right answer? Thx

Re: Exclude a product line from measure equation

You can mark yours and mark mine as helpful... but the reason mine gave an error was because I forgot a closing parenthesis..

If(GetFieldSelection([Business Segment]) = 'Fruits', Sum({<[Product Line] -= {'Grapes'}>}[capital]), Sum(capital))

Best,

Sunny

rglenn0780
Contributor

Re: Exclude a product line from measure equation

Perfect that works.

Do you know how I now add a product line in? I need the exact same equation only...

If(GetFieldSelection([Business Segment]) = 'Nuts', Sum({<[Product Line] -= {'Apples'}>}[capital]), Sum(capital))

I need to add the product line apples to the business segment nuts view.

Basically the opposite of what we did

Re: Exclude a product line from measure equation

You mean that Apple doesn't belong to Business Segment Nuts, but you want to add the result of product line Apples when Nuts is selected?

rglenn0780
Contributor

Re: Exclude a product line from measure equation

Yes mam

Re: Exclude a product line from measure equation

May be this

If(GetFieldSelection([Business Segment]) = 'Nuts', Sum({<[Product Line] = {'Apples'}>}[capital]) + Sum(capital), Sum(capital))

rglenn0780
Contributor

Re: Exclude a product line from measure equation

so basically adding the result of the product line capital numbers into the nuts business segment. just the opposite of removing grapes from fruits. Adding apples to nuts to show in the KPI.

Community Browser