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: 
ignasdukynas
Partner - Contributor III
Partner - Contributor III

Exclude filter for one dimension

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.

Capture.JPG

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

Capture2.JPG

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.

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
ignasdukynas
Partner - Contributor III
Partner - Contributor III
Author

Hello @gwassenaar

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

rubenmarin

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

ignasdukynas
Partner - Contributor III
Partner - Contributor III
Author

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.

Capture.JPG

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.

Capture.JPG

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

rubenmarin

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.

ignasdukynas
Partner - Contributor III
Partner - Contributor III
Author

Hello Ruben

Thank you a lot. You are exactly right.

Best regards,


Ignas