Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
If you want to ignore selections in Region you can change the expression to avg({<Region=,Customer={'John'}>}Amount)
Hello @gwassenaar
The average sales is wrong if I select one country. You can try the app and see that it does not work.
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
Hello Ruben,
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
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.
Hello Ruben
Thank you a lot. You are exactly right.
Best regards,
Ignas