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,

[Anonymous]

Yes, but it selects N_Month values higher than Variable2, then it calculates average for MYear.

I need to remove Clients who have less than Variable2 (do not include them to calculation)

In my test file, if you Industry=eee and N=10:

for Industry expression- Avg("Clients=B_ and H_"  TC)

for Total expression-- Avg("All Clients who have more than Variable2" TC)

avg({ $< Client=, N_Month={">$(=Variable2)"}>}[TC]) it removes data only in currents months, not completely Client

Regards,

[whiteline]

I need to remove Clients who have less than Variable2 (do not include them to calculation)

I think you don't understand the way set analysis is working (look at help there is useful info).

If you want to select clients you should select clients:

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

As you see it's the same condition as mentioned above.

[Anonymous]

I understand how works SET ANALYSIS. I misunderstand how select them.

I need 3 lines in my chart:

1st shows TOTAL (all clients who have more than Variable2)

2nd - INDUSTRY (all clients in selected INDUSTRY who have more than Variable2)

3rd - CLIENT (selected Client who has more than Variable2).

Please see my attached file.

I added 3 tables, which show the right result, if I select N=10 and Industry=eee and Client=B

avg({ $< Client=, N_Month={">$(=Variable2)"}>}[TC]) it was an idea to calculate all client in selected Industry.

If I select Client, Industry and Client lines are the same.

I don't know how select them

[Anonymous]

Here is the file

[Anonymous]

Whiteline, do you have any suggestions?

[whiteline]

1st shows TOTAL (all clients who have more than Variable2)

Use {1} as basic set in the above expression (instead of {$}), don't miss also Min() and Sum().

2nd - INDUSTRY (all clients in selected INDUSTRY who have more than Variable2)

Use set analysis to clear Client in Min() and Sum() in the above expression.

3rd - CLIENT (selected Client who has more than Variable2).

Just use the above expression.

[Anonymous]

I tried:

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

I can get the right result.

[Anonymous]

Hi Whiteline,

Did you try to fix my expression?

[Anonymous]

Maybe use somehow this expression to have clients for Industry

{<Client={$(=Replace(Concat(DISTINCT Client, ','), ' + ', ','))}>}

[Anonymous]

That's my original expression in Dimension

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

I want to keep conditional and count Clients in expression.

Regards,