Excluding certain values in a Measure

[malradi88]

Dear Qlik Community,

I hope this finds you well. I am currently trying to amend the measures I have in my scatter plot to respond to 'filename' filters.

The scatter plot I have has 'harvest' in the x axis, and 'income' in the y axis. The dimensions I am using are 'name' and 'flag' (flag as a mechanism to visualise pre & post on the plot).

The app is composed of 3 excel files (filename field inserted into app to distinguish between the 3 datasets):

- the first has a complete data set of 200  pre & post values

- the second only has 2 people with pre & post with the rest being null

- the third only has 1 person with pre & post with the rest being null

When I attempt to filter on the page using the filename field the 1st and 3rd datasets are accurate but not the second. The reason being that there are 9 people from the first excel file (the one with a complete dataset) that are repeated in the second one and even though those repeat people have no entries in the second excel file, their results from the first excel file are being shown when I click on the filename for the second excel file.

How can I exclude these repeat beneficiaries from the data set when I use the filename filter? As the 3 excel files are in fact 3 different projects, I do not want those repeat beneficiaries results showing when I click on the second excel file filename filter.

I have tried the following to make the harvest & income measures more specific but no luck!

Sum (Income)

Sum(aggr(sum(Distrinct [Income]), Name, Flag))

Sum(aggr(sum(Distrinct [Income]), Name, Flag, filename))

Sum (total <filename, flag> [Income])

Sum (total <filename, flag, Name> [Income])

Sum(Harvest)

Sum(aggr(sum(Distrinct [Harvest]), Name, Flag))

Sum(aggr(sum(Distrinct [Harvest]), Name, Flag, filename))

Sum (total <filename, flag> [Harvest])

Sum (total <filename, flag, Name> [Harvest])

I even tried adding a third dimension to make it Name&flag&filename but nothing worked.

If anyone has any ideas I would very much appreciate your help!

Best,

Mohammed

[simenkg]

Have you tried unchecking the box "Include null values"?

[malradi88]

Hi Simen,

Thank you for your message. Yes, I unchecked 'include null values' unfortunately it still displays those repeat values from the first excel sheet!

[sunny_talwar]

What about unchecking 'Include zero values' under Add-ons -> Data handling?

[malradi88]

Hi Sunny,

Yes I unchecked that as well but no luck

[sunny_talwar]

Would you be able to share an image of the chart after the selection?

[malradi88]

As you can see there are 9 pre & post points, but with the filename selection I made there should only be 2. It seems that the repeat ones that are present in the first file are showing even when I filter to only see the data in the second file.

[sunny_talwar]

This looks like more an issue with your data model to me... do you have Harvest and income in filename 1 and 2?

[malradi88]

Hi Sunny,

Please find enclosed the data model (sorry font is quite small). The bottom left table is the one that informs the scatter plot. It is constructed from 2 concatenated tables (one with pre flag and pre income/harvest info and the other with all post info). The field that associated with the main table in the middle is 'name' I tried removing name and having 'filename' be the linking field but no luck. I tried including both name and filename in the table but then got a synthetic

I double checked the excel files and the field names are exactly the same.

[sunny_talwar · Accepted solution]

Synthetic keys are not necessarily bad, but may be you do need to link on both of those... if that is the case and you really don't want synthetic key, you can use something like this

AutoNumber(name&filename) as Key

in both tables and then make sure to rename name and filename in one or both of the two tables....

I am not sure if this will resolve your issue, but worth giving it a shot