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
I always hear that synthetic keys are risky and can confuse data. I will have to read up on it!
