Solved: Re: exclusion - Qlik Community

Anonymous · ‎2012-09-18

Hi everyone,

I would like to add following condition: if any particular client has less that N (variable that I enter) records in any given month, and then take off this client from the table completely.

I misunderstood something.

It's my expression-which remove data in any given month,if condition applies

sum({$<N_Month={">$(=Variable2)"}>} N_Month)

I have dimension Client.

I'm expecting to see, if any value in the data is less than the input, hide the client.

Please see my link.

http://community.qlik.com/message/260343#260343

Thanks,

whiteline · ‎2012-09-19

I think this condition (bolded) will work:

=aggr(if(Min(aggr(Sum(N_Month), MYear, Client))>5 and (count(total<Client> distinct MYear)=count(total distinct MYear)), Client, null()), Client)

As you don't have data you can't calculate minimum. But you can count distinct MYear for a particular client and compare it with the total distinct number of MYear.

And its better to use Sum(N_Month) as your expression, becuse there are more than one value of N_Month for a particular MYear.

View solution in original post

Anonymous · ‎2012-09-18

Maybe use a macro?

Anonymous · ‎2012-09-19

How is it possible remove row?

Anonymous · ‎2012-09-19

Ani ideas?

whiteline · ‎2012-09-19

Hi.

Do you want to remove a row from a chart dynamically or from your data model (in loadscript) ?

Anonymous · ‎2012-09-19

From a chart dynamically.

whiteline · ‎2012-09-19

Then you could use calculcated dimension instead of Client dimension:

=aggr(if(Min(aggr(Sum(N_Month), MYear, Client))<5, Client, null()), Client)

Anonymous · ‎2012-09-19

Doesn't work. Something's wrong

Example is here

http://community.qlik.com/message/260343#260343

Thanks,

whiteline · ‎2012-09-19

Reverse the sign '<':

=aggr(if(Min(aggr(Sum(N_Month), MYear, Client))>5, Client, null()), Client)

You have blanks in your data. Do you mean exclude thouse clients also ?

Anonymous · ‎2012-09-19

Good idea!!!

But I need to connect to my Variable2=input variable.

I think we should exclude clients who have blanks in any months.