Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've posted about this before, but didn't find what I was looking for, so am reposting with additional info to hopefully get the answer I need!
I want to suppress data in Qlikview, however the tricky part is that the suppression needs to be dynamic. I.e. I don't want to get rid of the data, I just don't want it to be used in an expression if it meets certain criteria.
I thought I had found a solution in using modifiers, but that didn't work for reasons that I'll outline shortly, so I'm now hoping that a simple 'If' statement in my expression will work, but I'm not quite there...
Basically, my bar chart has one dimension with two values - Male/Female. My expression calculates exam results
e.g. 'sum([Points score])/sum(Num_exams_taken)'
In some instances (I have many, many charts calculating lots of different exam types), there will only be 3 or fewer of a gender taking the exam type. In that case I do not want their results shown (for data protection reasons).
Example table:
Exam centre | Gender | Candidate | Num_exams_taken | Points score |
Centre A | Female | 1 | 1 | 225 |
Centre B | Female | 0 | 0 | 0 |
Centre C | Female | 5 | 4 | 1980 |
Centre D | Female | 145 | 142 | 130800 |
Centre E | Female | 12 | 12 | 8385 |
Centre F | Female | 16 | 16 | 16245 |
Centre G | Female | 7 | 3 | 1545 |
Centre H | Female | 15 | 7 | 3690 |
Centre A | Male | 5 | 3 | 1695 |
Centre B | Male | 10 | 4 | 2640 |
Centre C | Male | 14 | 9 | 5325 |
Centre D | Male | 10 | 6 | 3990 |
Centre E | Male | 2 | 1 | 570 |
Centre F | Male | 1 | 1 | 1170 |
Centre G | Male | 6 | 3 | 1680 |
Centre H | Male | 35 | 33 | 30300 |
So when showing all results, I should have a bar for male and a bar for female. However, if I filter by 'Centre A' the female bar should not show, but the male bar should.
I tried using a modifier:
sum({<Candidate={'>=4'}>}[Points Score])/sum({<Candidate={'>=4'}>} Num_exams_taken)
but this discounts every row where the number of candidates is fewer than four, which is not what I want.
How can I change my expression so that it basically says:
For each dimension value, check if total number of candidates (based on current selections) is greater than 3, if it is display expression result, if it isn't, don't!
All help greatly appreciated!
Thanks
Jess
Perhaps like this: if(sum(Canditate)>=4,sum([Points score]),0)/if(sum(Canditate)>=4,sum(Num_exams_taken),0)
Thanks Gysbert!
In the end I tried:
IF(sum(Candidate)>=4,sum([Points Score])/sum(Num_exams_taken))
Which seems to be working. I had tried your suggestion previously but for some reason that was still causing issues.
Thanks so much for your help - fingers crossed this solution will continue to work in all cases!