Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zhbuyiqlik
Contributor II
Contributor II

Pivot Table Filter on Column

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

RegionABC
North100010001000
East200050
West2002000
South1000

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

RegionABC
North100010001000
West2002000
15 Replies
isingh30
Specialist
Specialist

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

Thank you!

isingh30
Specialist
Specialist

Main Script -

Temp:

LOAD Region,

     A,

     B,

     C

FROM

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

OmarBenSalem

alter ur measure as :

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

zhbuyiqlik
Contributor II
Contributor II
Author

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.

zhbuyiqlik
Contributor II
Contributor II
Author

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

OmarBenSalem

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

isingh30
Specialist
Specialist

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.

isingh30
Specialist
Specialist

Same for Values also

OmarBenSalem

can u share the EXACT structure of ur excel file?