topic Re: Dynamically exclude selected field value from chart but include all possible others in QlikView

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

mattdonkin — Sat, 16 Nov 2024 01:14:33 GMT

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

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

kaushiknsolanki — Wed, 19 Feb 2020 14:52:40 GMT

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)

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

mattdonkin — Wed, 19 Feb 2020 16:57:09 GMT

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

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

kaushiknsolanki — Wed, 19 Feb 2020 17:02:13 GMT

HI,

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

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

mattdonkin — Fri, 21 Feb 2020 10:04:33 GMT

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

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

mattdonkin — Mon, 24 Feb 2020 13:44:39 GMT

Hi Kaushik, were the details I provided helpful?

Thanks

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

Brett_Bleess — Tue, 24 Mar 2020 19:53:11 GMT

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