topic Re: Exclude filter for one dimension in App Development

Exclude filter for one dimension

ignasdukynas — Sat, 16 Sep 2017 10:52:36 GMT

Hello

I have a real issue trying to make a comparison table between region and country sales.

I get Amount (Region) numbers fine, but I struggle to get AVG(Amount) per country in the same table.

For example if I select country Belgium and Region East Flanders. Amount (Country) must disregard Region selection and show only country average.

I used this Set expression that does not work for me. I am also only interested in Customer John.

Could anybody help me to solve this puzzle? I attach the App. !

Would you be able to jump in @Sunny Talwar
Thank you in advance.

Re: Exclude filter for one dimension

Gysbert_Wassenaar — Sat, 16 Sep 2017 11:18:04 GMT

If you want to ignore selections in Region you can change the expression to avg({<Region=,Customer={'John'}>}Amount)

Re: Exclude filter for one dimension

ignasdukynas — Sat, 16 Sep 2017 11:37:52 GMT

Hello @gwassenaar

The average sales is wrong if I select one country. You can try the app and see that it does not work.

Re: Exclude filter for one dimension

rubenmarin — Sat, 16 Sep 2017 12:51:25 GMT

Hi Ignas, the expression in the master item of the uploaded app is different to the one posted by Gysbert, he uses commas to apply set analysis, not using '*', so:

sum({$<[Product]={"Apple"}>*$<Region=>} Amount) // Not ignores selections in region

sum({$<[Product]={"Apple"}, Region=>} Amount) // Ignores selection in region

Re: Exclude filter for one dimension

ignasdukynas — Sat, 16 Sep 2017 13:19:28 GMT

Hello Ruben,

rubenmarin

I am a bit confused. I upload a new app (https://community.qlik.com/servlet/JiveServlet/download/1345559-295883/Filter_exclusion.v2.qvf) with your formula except that it should be not a sum but average instead and instead of Product should be Customer. My new formula is this:

avg({$<[Customer]={"John"}, Region=>} Amount)

The Amount (Country) is simply wrong.

If you look at Banana.

The average Banana for USA should be 0.5 (Amount (Country)) the same as for Pie. But it shows in the table that it is 1.

Do you know what is wrong with the set analysis? How can I get the right numbers

Re: Exclude filter for one dimension

rubenmarin — Sun, 17 Sep 2017 09:06:06 GMT

Hi Ignas, I modified the expression in your first app, it used Sum and Product, I know you are asking about avg and Customer but I prefer to remark the differences in the sample app.

About this one I think what's happening is that you really don't have a record wich tells you have zero Bananas for Alaska, the chart shows zero but only because there is one Banana for Alabama, so it paints the cell for Banana-Alaska combination, but there is no data that really tells that (the excel doesn't have a row that tells "zero Banas for Alaska for John).

If there is only one record with a '1', the Avg should be '1', you'll need to generate the missing data (zeros) you need, creating all combinations needed like in the 'Generating all combinations of several fields' section of this document:

Generating Missing Data In QlikView

If you really need to count all zeros in the "A" you'll need a composite key with Customer, Product, Country and Region.

In "B" you can start with a table that relates Country with it's regions, join all customers (if customers have a region use it to join with the assigned region), and finally join with all products.

In "C" step you can add "0 as Amount"

Another option can be add the zeros in the source data, but one way or another it should exist to be counted.

Re: Exclude filter for one dimension

ignasdukynas — Sun, 17 Sep 2017 12:37:15 GMT

Hello Ruben

Thank you a lot. You are exactly right.

Best regards,

Ignas