topic Re: exclusion in QlikView

exclusion

Anonymous — Tue, 18 Sep 2012 19:12:10 GMT

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,

Re: exclusion

Anonymous — Tue, 18 Sep 2012 20:19:52 GMT

Maybe use a macro?

Re: exclusion

Anonymous — Wed, 19 Sep 2012 14:15:26 GMT

How is it possible remove row?

Re: exclusion

Anonymous — Wed, 19 Sep 2012 16:51:00 GMT

Ani ideas?

Re: exclusion

whiteline — Wed, 19 Sep 2012 17:01:46 GMT

Hi.

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

Re: exclusion

Anonymous — Wed, 19 Sep 2012 17:05:55 GMT

From a chart dynamically.

Re: exclusion

whiteline — Wed, 19 Sep 2012 17:10:05 GMT

Then you could use calculcated dimension instead of Client dimension:

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

Re: exclusion

Anonymous — Wed, 19 Sep 2012 17:14:27 GMT

Doesn't work. Something's wrong

Example is here

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

Thanks,

Re: exclusion

whiteline — Wed, 19 Sep 2012 17:18:04 GMT

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 ?

Re: exclusion

Anonymous — Wed, 19 Sep 2012 17:24:20 GMT

Good idea!!!

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

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

Re: exclusion

Anonymous — Wed, 19 Sep 2012 17:26:57 GMT

I fixed connection to my Variable.

How can we remove clients with blanks?

Re: exclusion

whiteline — Wed, 19 Sep 2012 17:34:20 GMT

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.

Re: exclusion

Anonymous — Wed, 19 Sep 2012 17:39:22 GMT

Awesome.

Yes, I use Sum(N_Month).

Thank you so much,

Can I ask you if I have some questions?

Re: exclusion

whiteline — Wed, 19 Sep 2012 17:47:29 GMT

It's better to ask the community

Re: exclusion

Anonymous — Wed, 19 Sep 2012 20:12:31 GMT

Could you help me to combine 2 expression in 1

Condition, which select my Client

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

and avg(MyField)

Thanks in advanced

Re: exclusion

Anonymous — Wed, 19 Sep 2012 20:23:54 GMT

Dimension MYear

It's a chart.

Re: exclusion

Anonymous — Wed, 19 Sep 2012 20:38:49 GMT

Dimension---MYear

It's a chart.

Re: exclusion

whiteline — Thu, 20 Sep 2012 07:08:35 GMT

Expression avg(MyField) with dimension MYear calculates average of MyFields for each MYear.

What do you want to calculate ?

Re: exclusion

Anonymous — Thu, 20 Sep 2012 14:34:45 GMT

Hello again,

I attached the file with expectations and example.

Thanks in advaced,

I played with min condition, but without result

Re: exclusion

whiteline — Thu, 20 Sep 2012 15:20:22 GMT

Your chart with MYear dimension.

What is this expression supposed to calculate ?

avg({ $< Client=, N_Month={">$(=Variable2)"}>}[TC])

(for now it takes current set of data, clears Client selection and selects N_Month values higher than Variable2, then it calculates average for MYear).