topic Re: Set Analysis is not working when I use String field. in QlikView

Set Analysis is not working when I use String field.

Mon, 11 May 2015 07:23:10 GMT

Hi Guys,

I have an application where I have Project Type Desc and %Project Type Key columns in a dimension.

I have 2 values

%Project Type Key Project Type Desc

1 Dev

2 Test

this Dimension Joining with Fact based on Key.

In a graph I am using below expressions

Expression1=Sum({<[Project Type Desc]={'Dev'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc]={'Test'}>}[Transaction Count])

I have list box Project Type Desc and displaying 2 values Dev and Test.

When I select Dev I am expecting to display only Expression1 but it is not filtering and displaying both.

If I change my expression to

Expression1=Sum({<[%Project Type Key]={'1'}>}[Transaction Count])

Expression2=Sum({<[%Project Type Key]={'2'}>}[Transaction Count])

It is working perfectly.

But I am hiding % so can't use this column.

I would like to know why it is not working with column Project Type Desc and working with %Project Type Key?

Re: Set Analysis is not working when I use String field.

Mon, 11 May 2015 07:37:04 GMT

Hi,

use this Expression

Sum([Transaction Count])

it will show result what u r expecting.

if u use above to 2 Expression it will show only

Expression 1

sum of [Transaction Count] for Dev

Expression 2

sum of [Transaction Count] for Test

because set analysis it will show alternative selections.

Regards,

Chennaiah.

Re: Set Analysis is not working when I use String field.

Anonymous — Mon, 11 May 2015 07:38:49 GMT

Hi,

I believe you are using [Project Type Desc] in your list box filter as well that what its not working .

You given this field in set analysis as hard coded [Project Type Desc]={'Dev'}

what you can do is,

use not equal to (-=)

Expression1=Sum({<[Project Type Desc]-={'Test'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc]-={'Dev'}>}[Transaction Count])

or you can create one more field in your script

[Project Type Desc] as [Project Type DescFilter]

Now use this field as listbox filter and [Project Type Desc] in set analysis .

It will work .

Thanks

BKC

Re: Set Analysis is not working when I use String field.

swuehl — Mon, 11 May 2015 07:40:45 GMT

That's because your Manual selection will be overridden by the set Analysis set modifier.

Try an intersection operator * to care for your user selection:

Expression1=Sum({<[Project Type Desc] *= {'Dev'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc] *= {'Test'}>}[Transaction Count])

Re: Set Analysis is not working when I use String field.

Mon, 11 May 2015 09:22:02 GMT

Hi All,

Will try all the suggestions and let you know the outcome.

Re: Set Analysis is not working when I use String field.

Mon, 11 May 2015 09:26:20 GMT

Use double quotes instead. Just try doing this

Expression1=Sum({<[Project Type Desc]={"Dev"}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc]={"'Test"}>}[Transaction Count])

Re: Set Analysis is not working when I use String field.

Tue, 12 May 2015 00:15:02 GMT

Hello Swuehl,

I try this

Expression1=Sum({<[Project Type Desc] *= {'Dev'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc] *= {'Test'}>}[Transaction Count])

It works as expected.

May I know what is the difference between

Expression1=Sum({<[Project Type Desc] *= {'Dev'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc] *= {'Test'}>}[Transaction Count])

and

Expression1=Sum({<[Project Type Desc] = {'Dev'}>}[Transaction Count])

Expression2=Sum({<[Project Type Desc] = {'Test'}>}[Transaction Count])

Why Expression does not work without *?

Re: Set Analysis is not working when I use String field.

swuehl — Tue, 12 May 2015 07:48:49 GMT

Your original Expression does ignore the user selection in Project Type Desc field and sets an new value.

My expression doesn't ignore user selection, it will use the intersection of user selection in that field (or possible values of that field) and the fixed value you are using.

More? For example here:

https://iqlik.wordpress.com/2010/11/21/the-magic-of-set-analysis-part-iii/