Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prakash_1981
Contributor II
Contributor II

How to filter out some data in Qlik pivot

Hi Guys,

I am new to Qlik sense . I am trying to create a pivot table with Couple of data source.

In that i need to remove some ROWS from the view. TO be filtered out.

Can anybody help in this.

Ex...I am getting X1,X2,X3,X4 row data & C1,C2,C3,C4 column data.

I dont want X3 row data.

Please help.

Thanks

Prakash

6 Replies
OmarBenSalem
Partner
Partner

X3 is a value in ur dimension?

In ur measure;

change it as follow:

from : sum(Measure)

to: sum({<YourDim=-{'X3'}>}Measure)

clondono
Creator III
Creator III

Use set analysis.

For example if you want to count the number of sales and want to exclude those with Status of 'Closed':

Count({<Status-={'Closed'}>}Sales)

Check this blog about set analysis:

A Primer on Set Analysis

I hope this helps.

Thanks,

Carlos

JustinDallas
Specialist II
Specialist II

Will it work with the MINUSEQUALS?  I thought it had to be EQUALSMINUS.

OmarBenSalem
Partner
Partner

Hi Justin, that a good question !

there is a difference between the 2; let me explain:

Let's suppose we have a Dimension called Dim containing these values:

A

B

C

D

1) sum({<Dim=-{'A'}>}Measure)

is like writting :

sum({<Dim={*}-{'A'}>}Measure)

which means, sum Measure of All the values of Dim but the value 'A'.

<=> All Dim's values minus A no matter the selection made in the filter Dim

So, even if u select value B for exp; it will always return the sum of B C and D


2)sum({<Dim-={'A'}>}Measure)

return the sum measure of what u select minus A

which means, if we select nothing, it returns the sum of B C and D

But if we select, B; it return only B

if we select A and B and C, it will return B and C

<=> what u select minus A


Hope that was clear?

prakash_1981
Contributor II
Contributor II
Author

Thanks guys...

It got solved.

I got stuck in below issue:

I am having a Category field and associated data in table.In this category i have multiple type of categories.

I am having another table Category where Categories are grouped in to 3 major group.

Now i need a data view which shows all details(count) based on the category groups.

I am struggling to link two tables and get this report done.

Can anyone help.

JustinDallas
Specialist II
Specialist II

Oh wow, thanks Omar.  I had never seen that notation before.  I guess that's why I roam the forums, to learn things and fill out my toolbelt.