Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Use set analysis without calculation

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.

8 Replies
venkatg6759
Contributor III

Re: Use set analysis without calculation

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

jaimeaguilar
Valued Contributor II

Re: Use set analysis without calculation

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


Not applicable

Re: Use set analysis without calculation

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

Not applicable

Re: Use set analysis without calculation

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

Not applicable

Re: Use set analysis without calculation

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?

Not applicable

Re: Use set analysis without calculation

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
Valued Contributor II

Re: Use set analysis without calculation

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

Not applicable

Re: Use set analysis without calculation

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.

Community Browser