exclusion

[Anonymous]

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]

I understand how works SET ANALYSIS.

I gave you an expression:

avg({$<Client={"=Min(aggr(Sum(N_Month), MYear, Client))>$(Variable2) and (count(total<Client> distinct MYear)=count(total distinct MYear))"}>}[TC])

An said how to modify it for a particular line.

Have you tried something ? What's wrong ?

[Anonymous]

Hi Whiteline,

Thanks for your help.

Your expression work perfect. But I have multibox, where I select my Industry and Client.

When I select Industry, your expression shows result Industry. When I select Client, Industry and Client show the same result.

I used =avg({ $< Client=, N_Month={">$(=Variable2)"}, MYear=P(MYear)>}[TC]) this expression.

It ignores my Client selection and removes data only in cells(not row completely)

I need to show TOTAL, Industry and Client in one chart.

Please see my attached file. There is a chart, multibox and right results, if you select Industry=eee and minimum N=10.

In sheet 1 I have text box, where I want to see TOTAL count clients( ignore Industry and Client levels)

Thank you again,

[whiteline]

For Industry:

=avg({$<Client={"=Min({1} aggr(Sum({1} N_Month), MYear, Client))>$(Variable2) and (count({1} total<Client> distinct

MYear)=count({1} total distinct MYear))"}>}[TC])

[Anonymous]

That's exactly as I want.

But for TOTAL, which include all clients across all Industry.

Ignore Industry=, Client=?

[whiteline]

Why didn't you even try to do it yourself ?

I said you above, how to solve it.

=avg({1<Client={"=Min({1} aggr(Sum({1} N_Month), MYear, Client))>$(Variable2) and (count({1} total<Client> distinct MYear)=count({1} total distinct MYear))"}>}[TC])

[Anonymous]

I tried.

This expression doesn't work

=avg({$<Client={"=Min({1} aggr(Sum({1} N_Month), MYear, Client))>$(Variable2) and (count({1} total<Client> distinct

MYear)=count({1} total distinct MYear))"}>}[TC])

I applied this expression, I got different results.

Some clients were ignored.

I try different ways, but no results.

[Anonymous]

That's expression works right

Count({$<Client={"=Min(aggr(Sum(N_Month), MYear, Client))>$(Variable2) and (count(total<Client> distinct MYear)=count(total distinct MYear))"}>}

DISTINCT (Client))

but it dependent on Selections.

That's expression

=avg({$<Client={"=Min({1} aggr(Sum({1} N_Month), MYear, Client))>$(Variable2) and (count({1} total<Client> distinct

MYear)=count({1} total distinct MYear))"}>}[TC])

works well on test.qw file, but not on original data (it's same data, but only more clients and industries)

[whiteline]

Some clients were ignored.

Could you explain ?

In my case the result on Chart is exactly the same as total row in the table 'List of clients'.

[Anonymous]

Please see my new file.

I wrote some comments there.

Regards,

[whiteline]

You can't use MYear selections with this calculated dimension:

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

Otherwise it will check only selected periods. As you can see it just keeps some additional clients.

You should correct it with {1} like in avg() expression.