topic Re: Use set analysis without calculation in QlikView

Use set analysis without calculation

Wed, 18 Jun 2014 15:03:52 GMT

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.

Re: Use set analysis without calculation

venkatg6759 — Wed, 18 Jun 2014 15:06:18 GMT

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

Re: Use set analysis without calculation

jaimeaguilar — Wed, 18 Jun 2014 15:08:40 GMT

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

Re: Use set analysis without calculation

Wed, 18 Jun 2014 15:21:43 GMT

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

Re: Use set analysis without calculation

Wed, 18 Jun 2014 15:26:19 GMT

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

Re: Use set analysis without calculation

Wed, 18 Jun 2014 15:29:27 GMT

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?

Re: Use set analysis without calculation

Wed, 18 Jun 2014 15:33:05 GMT

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.

Re: Use set analysis without calculation

jaimeaguilar — Wed, 18 Jun 2014 15:36:11 GMT

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

Re: Use set analysis without calculation

Wed, 18 Jun 2014 15:43:08 GMT

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.