Set Analysis is not working when I use String field.

[#1]

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?

[#2]

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.

[Anonymous]

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

[swuehl · Accepted solution]

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])

[#5]

Hi All,

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

[#6]

Use double quotes instead. Just try doing this

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

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

[#7]

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 *?

[swuehl]

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/