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: 
Not applicable

Table Columns in Filters - Stacked Bar Chart, It is Interesting but challenging

Hi,

This is probably a brain teaser and Please help me with simple logic.


I have 2 scenarios so that, if you can suggest me solution for any one of the scenario my problem will be solved. The help is appreciated.


Scenario 1:


Here is the table.

Merchant    Phone-sales_Count Store-sales_Count Online-sales_Count
Best Buy300020001000
Apple230034003200
Walmart980056004300
Costco160014001900

Now I am plotted a Stacked Bar Chart with below parameters:

Dimension:        Merchant_Name

Measures:          Phone-sales_Count, Store-sales_Count, Online-sales_Count

Now I want to have a Filters which actually filters between the measures ( Phone-sales_Count, Store-sales_Count, Online-sales_Count), so that the stacked chart will show the selected measure data only.


Filter_Sales
Phone-sales_Count
Store-sales_Count
Online-sales_Count

Please help me How can I do that.


Scenario 2:


I changed the table and data format to below as the filter does not work for me.

But this time, the filter worked but the stacked chart is not working because I can select only count.

Merchant  Type_of_SaleCount
Best BuyPhone-sales_Count2000
ApplePhone-sales_Count3400
WalmartPhone-sales_Count5600
CostcoPhone-sales_Count1400

If you can please give me solution for any one of the scenario that will solve the purpose. Thanks in advance.

1 Solution

Accepted Solutions
Not applicable
Author

Is this is the view you're after

Before

Filter1.JPG

After

Filter2.JPG

This is a basic bar chart with two dimension Merchant, Type of Sales and Measure sum(Count)

Used your Scenario 2 Table.

View solution in original post

10 Replies
ogster1974
Partner - Master II
Partner - Master II

This example shoukd show you how to achieve option one by setting your measure you use by a variable under a button.

---

Not applicable
Author

Hello,

You can probably use a variable as Filter and use the following variable to show only the measure you wanted.

Variable Name = vDisplay

Alternative values for measure

1- O_C

2-P_C

3-S_C

Measure =

if($(vDisplay) = 1,Sum(O_C),

if($(vDisplay) = 2,Sum(P_C),

if($(vDisplay) = 3,Sum(S_C), 0)))

Hope this helps.

Good Day

H.K

Not applicable
Author

Thanks for the reply I tried but the major thing is I need a stacked chart before applying filters. Thanks.

Not applicable
Author

Thanks for the reply I tried but the major thing is I need a stacked chart before applying filters. Thanks.

Not applicable
Author

Is this is the view you're after

Before

Filter1.JPG

After

Filter2.JPG

This is a basic bar chart with two dimension Merchant, Type of Sales and Measure sum(Count)

Used your Scenario 2 Table.

ogster1974
Partner - Master II
Partner - Master II

I was going to say thats easy you go here... but its not there anymore... you used to have an option to set the apperance as stacked.  Anyone else noticed this has disapeared? Im currently on 3.1.

rahulpawarb
Specialist III
Specialist III

Hello Chaitanya,

Trust that you are doing well!

I have used data given in scenario 2 (refer below given sample script):

NewData:

LOAD * INLINE [

Merchant, Type_of_Sale, Count

Best Buy, Phone-sales_Count, 3000

Best Buy, Store-sales_Count, 2000

Best Buy, Online-sales_Count, 1000

Apple, Phone-sales_Count, 2300

Apple, Store-sales_Count, 3400

Apple, Online-sales_Count, 3200

Walmart, Phone-sales_Count, 9800

Walmart, Store-sales_Count, 5600

Walmart, Online-sales_Count, 4300

Costco, Phone-sales_Count, 1600

Costco, Store-sales_Count, 1400

Costco, Online-sales_Count, 1900

];

Post reloading data I created a stacked bar chart with below parameters:

Dimension:

1. Merchant

Measures:

1. Sum(If(Type_of_Sale='Phone-sales_Count',Count,0))  // Phone Sales

2. Sum(If(Type_of_Sale='Store-sales_Count',Count,0))  // Store Sales

3. Sum(If(Type_of_Sale='Online-sales_Count',Count,0))  // Online Sales

Hope this will be of help.

Regards!

Rahul

Not applicable
Author

Ok the following method will help you with a small caveat. You will have 4 values in the variable Phone, Online, Shop and Stacked. If user selects stacked it gives the complete graph.

Variable Name = vDisplay

Alternative values for Variable

1- P_C

2-S_C

3-O_C

4, Stacked

Measure P_C

if($(vDisplay) = 2,0,

if($(vDisplay) = 3,0, Sum(P_C)))

Measure S_C

if($(vDisplay) = 1,0,

if($(vDisplay) = 3,0, Sum(S_C)))

and

Measure O_C

if($(vDisplay) = 1,0,

if($(vDisplay) = 2,0, Sum(P_C)))

Good Day

Hari

Not applicable
Author

I got the solution before your post, But you nailed it. It is the same solution what I did. that works for scenario 2. Thank you!