Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
HI,
Can you share the type of chart you are using along with Dimension ,Expressions and the selections that you have done.
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
Hi Kaushik, were the details I provided helpful?
Thanks
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