Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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

1 Solution

Accepted Solutions
Brett_Bleess
Support (Former)
Support (Former)

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
Luminary Alumni
Luminary Alumni

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)

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
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
Luminary Alumni
Luminary Alumni

HI,

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

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
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
Support (Former)
Support (Former)

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