topic Re: Pivot Table Filter on Column in Visualization and Usability

Pivot Table Filter on Column

zhbuyiqlik — Thu, 22 Feb 2018 21:07:44 GMT

I would like to do the following in Qlik Sense. I am wondering if there is a way to do it using a pivot table. Alternatively, I can produce a straight table, and do the same operation. But I'd prefer to use pivot table to do it. Thanks!

I created a pivot table using like table 1

Table 1

Region	A	B	C
North	1000	1000	1000
East	2000	50
West		200	2000
South	1000

Now I want to filter on product C so that only show rows where sales for C is greater than 0. I want the product selection to be user defined. i.e. If the user select product A, then we would filter on A using the same condition.

Table 2

Region	A	B	C
North	1000	1000	1000
West		200	2000

Re: Pivot Table Filter on Column

isingh30 — Thu, 22 Feb 2018 21:39:10 GMT

One Question - You want to do this in the main script or UI?

Thank you!

Re: Pivot Table Filter on Column

isingh30 — Thu, 22 Feb 2018 21:40:42 GMT

Main Script -

Temp:

LOAD Region,

FROM

(ooxml, embedded labels, table is Sheet1)where C > 0;

Re: Pivot Table Filter on Column

OmarBenSalem — Thu, 22 Feb 2018 21:46:15 GMT

alter ur measure as :

sum({<Region={'North','West'}>}Measure)

Re: Pivot Table Filter on Column

zhbuyiqlik — Thu, 22 Feb 2018 21:46:26 GMT

Thank you for answering. I am familiar with programming but have not done scripts in Qlik Sense. Guess I need a UI solution. But I will try to implement your script solution (Also as a way to learn Qlik Sense Script).

Reading your script, how do I make "C" to be user selected? I imagine I need to have user select on the product dimension and pass the user selection to the script?

Thanks a lot.

Re: Pivot Table Filter on Column

zhbuyiqlik — Thu, 22 Feb 2018 21:48:48 GMT

Thanks,

1. Is this just going to give me the sum of North and West? I need the North and West rows listed separately.

2. Can I do this based on user selection? --i.e. If user select C, it will filter based on the condition measure (C) > 0

Re: Pivot Table Filter on Column

OmarBenSalem — Thu, 22 Feb 2018 21:52:19 GMT

1)It will give u only North and West (seperately) if u add ur Region dimension as a dimension in ur table as shown.

2)Qlik will always show only the dimensions/values associated to the user selection; unless u force it to show sthing specific

Ps: try it, and u'll see

Re: Pivot Table Filter on Column

isingh30 — Thu, 22 Feb 2018 21:52:26 GMT

I recommend you to use cross load on your data first. Your data is not organised. There has to be a separate column for Product - A,B,C.

Check this - The Crosstable Load

Thank you.

Re: Pivot Table Filter on Column

isingh30 — Thu, 22 Feb 2018 21:53:18 GMT

Same for Values also

Re: Pivot Table Filter on Column

OmarBenSalem — Thu, 22 Feb 2018 21:59:26 GMT

can u share the EXACT structure of ur excel file?

Re: Pivot Table Filter on Column

zhbuyiqlik — Thu, 22 Feb 2018 22:01:34 GMT

If I understood you correctly, my original data is "organised" with one product column and a value column (sales). What I am showing in the original post is a pivot table I created in Qlik Sense based on the original data. The structure of the original data is like the following:

Client ID	Region	Product	Sales
1	North	A	1000
2	North	B	500
3	East	C	100
4	East	B	1000
5	Sourth	A	500
6	Sourth	C	200
7	West	A	300

Re: Pivot Table Filter on Column

zhbuyiqlik — Thu, 22 Feb 2018 22:04:23 GMT

Not exact number as shown in the original post, but this shows the structure.

Client ID	Region	Product	Sales
1	North	A	1000
2	North	B	500
3	East	C	100
4	East	B	1000
5	South	A	500
6	South	C	200
7	West	A	300

Re: Pivot Table Filter on Column

OmarBenSalem — Thu, 22 Feb 2018 22:07:02 GMT

no need for the crosstable load so; just do as I've told u at first and tell me what happens

Re: Pivot Table Filter on Column

Thiago_Justen_ — Thu, 22 Feb 2018 22:11:21 GMT

May be this:

Sum({<Product={"=Sum(Sales)>0"},Region={"$(=GetFieldSelections(Region))"}>}Sales)

Here we have to filters that will be done:

Region and Product

Re: Pivot Table Filter on Column

Thiago_Justen_ — Thu, 22 Feb 2018 22:15:51 GMT

Here is the result:

Re: Pivot Table Filter on Column

OmarBenSalem — Thu, 22 Feb 2018 22:22:34 GMT

Sum({<Region={'North','West'}>}Sales)

result: