Use set analysis without calculation

[#1]

Hi everyone,

I am interested in using set analysis to simply narrow down my dimension in a straight table.

I would like to only show the [Account Number]s in my data where Field1 = 1 and Field2 = 'A'. Shown below is what I have so far, but I'm unsure if set analysis is capable of this.

({1<Field1={1}, Field2={'A'}>}[Account Number])

Does anybody know the best way to do this? Thanks.

[venkatg6759]

=aggr(only({$<Field1={1}, Field2={'A'}>}[Account Number]),[Account Number])

[jaimeaguilar]

Hi,

if you don't want to perform any aggregation function to your field like sum, max, count, etc you can use Only. This is the default qlikview aggregation function, but you need it in order to use set analysis. your expression would be something like this:

Only({1<Field1={1}, Field2={'A'}>}[Account Number])

It is important to mention that the result of this expression should be only One value (in this case one Account Number), otherwise it won't work,

regards

[#4]

Matthew,

You did say narrow down your dimension?

You could define a calculated dimension as

if (field1='1',if(field2='A',[account number]))

your expression could then count invoices for that account, sum the value or what ever.

I did that from memory so let me know if thats what you needed?

Richard

[#5]

So, there is no way to use set analysis if I want my dimension to contain more than one row?

[#6]

This works, but the reason that I wanted to use set analysis was so I could prevent this chart from being narrowed down by selections (using the 1 as an identifier). Am I able to do this with an if statement?

[#7]

Hi,

If I understand you have a straigth table with dimension [Account Number] and some expression (sum(MyExpresion)), and you need show only [Account Number] wiht condition Field1=1 and Field2='A' so I try this:

Dimension: [Account Number]

Expression: Sum({$<Field1={1}, Field2={'A'}>} MyExpression)

And you get some information about Account Number with Field1=1 and Field2='A'.

Best regards.

[jaimeaguilar]

Your dimension can contain more than one row, but the combinations of your filters need to be unique, in order to use only,

for example:

Field1   Field2            AccountNumber:

1           A                           23

1           B                           53

2           A                           42

2           C                           32

[#9]

Ah, thanks for elaborating.

Forget that previous suggestion and use the following type of set analysis on the expression

{1<Field1={'1'}, Field2={'A'}>}

set the dimension to account number, and supress null values.