Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

prakash_1981
New Contributor

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
Esteemed Contributor

Re: How to filter out some data in Qlik pivot

X3 is a value in ur dimension?

In ur measure;

change it as follow:

from : sum(Measure)

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

clondono
Contributor III

Re: How to filter out some data in Qlik pivot

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

drillnaut
Valued Contributor

Re: How to filter out some data in Qlik pivot

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

omarbensalem
Esteemed Contributor

Re: How to filter out some data in Qlik pivot

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
New Contributor

Re: How to filter out some data in Qlik pivot

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.

drillnaut
Valued Contributor

Re: How to filter out some data in Qlik pivot

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.