Alternative to using Set within an Aggr

[#1]

I have a table with two dimensions and one expression: Client.Name, Industry, sum(Revenue)

I want to adjust my dimensions to only show clients in Industry 'A' who have <$1000 in revenue.  I currently do this using two dimensions and Suppress Null:

=if(aggr(sum(Revenue),Client.Name)<1000,Client.Name)

=if(Industry='A',Industry) [I hide this column]

Is it possible to do this using only a single dimension?  The trouble i'm having is that I would normally use set analysis to explicitly define Industry, but my understanding is you can't use set within the Aggr.

I ultimately want to create a table with one calculated dimension, Client.Name, defined as:  If Client's Revenue >=1000 OR [Client's Revenue < 1000 AND Industry = 'A'], then show Client.  I'm having a lot of difficulty getting this in a table.  Greatly appreciate any help.

[sunny_talwar]

The opposite doesn't work, i.e., Aggr doesn't work the way you would expect within set analysis, but set will work within Aggr.

But for your case, you can try something like this:

=If(Aggr(Sum(Revenue), Client.Name)<1000 and Industry = 'A', Client.Name)

=If((Aggr(Sum(Revenue), Client.Name) >= 1000) or (Aggr(Sum(Revenue), Client.Name) < 1000 and Industry = 'A'), Client.Name)

HTH

Best,

Sunny

[MK_QSL]

I have used

Customer as Dimension

with below expression...

Change According to your need..

Aggr(IF(SUM({<Brand = {'B4'}>}Sales)>=10000, SUM({<Brand = {'B4'}>}Sales)),Customer,Brand)

[stigchel]

I like to avoid calculated dimensions and do the restriction in the expression instead. In your case something like:

Sum({<Client.Name={'=Aggr(Sum(Sales),Customer)>=1000'}>+<Client.Name={'=Aggr(Sum({<Industry={"A"}>} Sales),Customer)<1000'}>} Sales)

And just Client.Name as Dimension

[#5]

Thanks, this is closest to working.  I had already tried Sunindia's suggestion, but it didn't work --- for some reason, Qlikview doesnt like these "or" statements.

I tested your formula by making two tables and then comparing against your aggregate table:

     Table 1 has dimension: 

          =if(aggr(sum(Revenue),Client.Name)>=1000,Client.Name))

     Table 2 has the dimensions i originally noted:

          =if(aggr(sum(Revenue),Client.Name)<1000,Client.Name)

          =if(Industry='A',Industry)

Your aggregate table correctly brings in all records from my Table 1 and Table 2 ... however, it also brings in records with <$1000 revenue that are in Industry B .. not sure why this occurs as the set analysis explicitly says Industry A ... any idea why this would occur? 

[#6]

I realized that QVW is ignoring part of your code in bold below ... I get the same results whether I include / exclude it or change the value of A

Sum(

{<Client.Name={'=Aggr(Sum(Sales),Customer)>=1000'}>

+

<Client.Name={'=Aggr(Sum({<Industry={"A"}>} Sales),Customer)<1000'}>

}

Sales)

[stigchel]

That's strange, are you sure these customers don't (also) belong to Industry A?

I've made a test qvw and it does include Customers from the selected Industry and no others.See attached

[#8]

Thank you for confirming.  Client and Industry actually have a one-to-many relationship ...i.e., Client 1 can have $x revenues tied to Industry A, and $y revenues tied to Industry B.  Would that impact formula?  Seemed to me like it should still work...

[#9]

Here's what's happening...I used the same dimension / expression as you outlined.

Dimension: Client.Name

Expression: Sum({<Client.Name={'=Aggr(Sum(Sales),Customer)>=1000'}>+<Client.Name={'=Aggr(Sum({<Industry={"A"}>} Sales),Customer)<1000'}>}Sales)

Regardless of what is in the expression's set analysis, the number of rows remains the same ... e.g., if I change the first set condition to be a very large number ">=1000000000000" instead of ">=1000", the number of rows is not impacted ... the only thing that changes is the value of the expression (for example, a client with $2000 now shows as $0 instead of simply disappearing from the table) 

Any idea how to adjust?

[stigchel]

Important to realize is that the set analysis defines the selections, in this case, of client names who meet the condition. If those 'selected' clients also have sales in an other industry, those will be included in the results