Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor II

Exclude with IF in SUM expression Set-Analysis

Hi All,

let's assume I have the following categories and the measure Nett Amount:

Category

Subcategory

A1
2
3
B
C
D

As measure, I want to use the SUM of sales of all the categories: SUM([Nett Amount])

To get an accurate overview of the sales, I want the SUM Nett Amount of all the categories without subcategory A2.

So there is one field that has to be excluded.

I tried such an expression like: SUM((IF(Not match(Subcategory,2)Subcategory))[Nett Amount]), but that didn't work.

How do I write such an expression?


Thanks a lot!

Mike

1 Solution

Accepted Solutions

Re: Exclude with IF in SUM expression Set-Analysis

Seems to be working, isn't it?

Capture.PNG

12 Replies

Re: Exclude with IF in SUM expression Set-Analysis

May be this

Sum({<Subcategory -= {2}>} [Nett Amount])

Highlighted
mslottje
Contributor II

Re: Exclude with IF in SUM expression Set-Analysis

Hmmmm that didn't work. The subcategory is still in there..

Re: Exclude with IF in SUM expression Set-Analysis

Would you be able to share a sample to show the issue?

mslottje
Contributor II

Re: Exclude with IF in SUM expression Set-Analysis

Hmmm that's a bit difficult because I have to share our sales data.

For our analysis, we want to exclude one certain item number which is messing up our margin.

Of course I can do this with a filter pane, but I'm trying to find a solution where I can calculate our sales per i.e. customer withouth this specific item number.

Should I create some example data?

Re: Exclude with IF in SUM expression Set-Analysis

Example data should be helpful, but just to make sure, were you sure to put a small negative sign in front of equal?

Sum({<Subcategory -= {2}>} [Nett Amount])

mslottje
Contributor II

Re: Exclude with IF in SUM expression Set-Analysis

Yeah I have the negative sign in front of the equal sign. I will post some example data later on today!

To be continuedSmiley Happy

mslottje
Contributor II

Re: Exclude with IF in SUM expression Set-Analysis

Attached a simple sample file.

The file shows customers, item numbers, nett amount (revenue) and margin %.

When you have a look at the data, you'll see that item number C has been sold only one time with a high revenue and a high margin.

This item number C is an item which i.e. does not belong to the normal assortment of the company and should therefore be excluded from the analysis.

Hopefully this helps!

german24
New Contributor III

Re: Exclude with IF in SUM expression Set-Analysis

I hope it helps...

Regards.

German

Re: Exclude with IF in SUM expression Set-Analysis

Seems to be working, isn't it?

Capture.PNG

Community Browser