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: 
mattdonkin
Contributor
Contributor

Dynamically exclude selected field value from chart but include all possible others

Hi,

I have a multi box which includes 2 fields - market and customer. The selection then drives a pivot table.

At the moment, I select market = "Spain" and customer = "ABC" and the pivot table updates with the relevant calculated fields relating to customer "ABC"

I would also like to have a copy of this pivot table but this time displaying all other customers within Spain except for "ABC". This way I can then run a comparison between customer "ABC" and the average of all other customers in that market.

I can do this via set analysis but I'd like to build it so that it runs off whatever value is selected in the multi box -  ie, if I select customer = "DEF" then pivot table 1 gives me all calculations relevant to "DEF" and pivot table 2 gives me all customers except "DEF".

That way I can easily hand it over to users without requiring them to amend the set analysis.

Thanks

Matt

Labels (4)
1 Solution

Accepted Solutions
Brett_Bleess
Former Employee
Former Employee

Matt, try the following specific Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

If that does not quite do it, here is the base area link where you can search further on your own, there are hundreds of how-to posts in this area and a bunch on Set Analysis, so I think you may be able to find what you need here:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

If users going to select only one value in both the filters then you can try below expressions.

1. To include the values

Sum({<Market = {"$(=Maxstring(Market))"},Customer = {"$(=Maxstring(Customer))"} >}Sales)

2. To exclude the customer

Sum({<Market = {"$(=Maxstring(Market))"},Customer -= {"$(=Maxstring(Customer))"} >}Sales)

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mattdonkin
Contributor
Contributor
Author

 Hi Kaushik, thanks for your reply.

I've tried this and the "include" expression returns just the customer selected but the "exclude" expression doesn't return anything.

If I select no customer then the "exclude" expression returns values for all customers in that market.

Not sure if I'm missing something?

Thanks

 

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

Can you share the type of chart you are using along with Dimension ,Expressions and the selections that you have done.

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mattdonkin
Contributor
Contributor
Author

Hi, I have 2 pivot table chart types. Both are identical in the dimensions shown - Market and Customer. The first table has the expression to include just the values for the Customer selected, and the second table has the expression to include all other customer values except the customer selected.

What I am seeing is that - with no specific customer selected, the first table ("include") shows nothing whilst the second table ("exclude") shows values for every customer.

If I then select a specific customer, the first table shows the values for that customer but the second table shows nothing.

The expression I'm using to include is:

sum({<SelDate={">=$(=date(monthstart(max(SelDate),-12),'MMMM YYYY')) <=$(=date(max(SelDate),'MMMM YYYY'))"},Market = {"$(=Maxstring(Market))"},[Customer] = {"$(=MaxString([Customer]))"}>}[Volume])

The expression to exclude is:-

sum({<SelDate={">=$(=date(monthstart(max(SelDate),-12),'MMMM YYYY')) <=$(=date(max(SelDate),'MMMM YYYY'))"},Market = {"$(=Maxstring(Market))"},[Customer] -= {"$(=MaxString([Customer]))"}>}[Volume])

I hope this makes sense!

Thanks

mattdonkin
Contributor
Contributor
Author

Hi Kaushik, were the details I provided helpful?

Thanks

Brett_Bleess
Former Employee
Former Employee

Matt, try the following specific Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

If that does not quite do it, here is the base area link where you can search further on your own, there are hundreds of how-to posts in this area and a bunch on Set Analysis, so I think you may be able to find what you need here:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.