Dynamic data suppression

jessica_webb — Fri, 09 Oct 2015 09:08:03 GMT

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

Re: Dynamic data suppression

Gysbert_Wassenaar — Fri, 09 Oct 2015 14:53:29 GMT

Perhaps like this: if(sum(Canditate)>=4,sum([Points score]),0)/if(sum(Canditate)>=4,sum(Num_exams_taken),0)

Re: Dynamic data suppression

jessica_webb — Mon, 12 Oct 2015 09:02:58 GMT

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!

topic Dynamic data suppression in QlikView

Dynamic data suppression

Re: Dynamic data suppression

Re: Dynamic data suppression