topic Re: Alternative to using Set within an Aggr in QlikView

Alternative to using Set within an Aggr

Thu, 23 Apr 2015 15:47:53 GMT

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.

Re: Alternative to using Set within an Aggr

sunny_talwar — Thu, 23 Apr 2015 16:23:55 GMT

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

Re: Alternative to using Set within an Aggr

MK_QSL — Thu, 23 Apr 2015 16:25:58 GMT

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)

Re: Alternative to using Set within an Aggr

stigchel — Thu, 23 Apr 2015 16:51:20 GMT

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

Re: Alternative to using Set within an Aggr

Thu, 23 Apr 2015 19:47:14 GMT

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?

Re: Alternative to using Set within an Aggr

Thu, 23 Apr 2015 19:58:05 GMT

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)

Re: Alternative to using Set within an Aggr

stigchel — Fri, 24 Apr 2015 10:13:40 GMT

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

Re: Alternative to using Set within an Aggr

Fri, 24 Apr 2015 17:28:16 GMT

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...

Re: Alternative to using Set within an Aggr

Fri, 24 Apr 2015 18:58:37 GMT

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?

Re: Alternative to using Set within an Aggr

stigchel — Fri, 24 Apr 2015 19:49:14 GMT

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

Re: Alternative to using Set within an Aggr

rwunderlich — Fri, 24 Apr 2015 20:15:28 GMT

As a Calculated Dimension I think it would be:

=aggr(only({<Customer={"=sum(Sales)>1000"}>+<Industry={A}>}Customer),Customer)

I think the <1000 test is unnecessary. They qualify either as being above 1000 or Industry A.

-Rob

Re: Alternative to using Set within an Aggr

Fri, 24 Apr 2015 22:02:35 GMT

thanks so much, this worked

Re: Alternative to using Set within an Aggr

Fri, 24 Apr 2015 22:03:09 GMT

i think my data set is just a little funky and not agreeing with it ... b/c as you said, your sample works ... thanks though, appreciate the help.