Using the numeric value of a dual type field as field value definition in a set modifier

[wdonckers]

Hi,

I have loaded a field called 'Rubriek' from Excel. Actually in Excel there's a RubriekID (which is a numeric key) and Rubriek(which is a text).

In the script I loaded it as follows:

LOAD dual(Rubriek,RubriekID) as Rubriek,...

That seems to work fine, I can use this dimension in objects , sort them bu numeric value and choose to display the num or the string.

In an expression where i use set analysis the following gives no results:

sum({$<Rubriek={1320}>}[Sales for the Period_SALES])

If I write the following instead it does work:

sum({$<Rubriek={'     SOFTWARE'}>}[Sales for the Period_SALES])

How to overcome this: Working with numericals is supposed to be faster and filtering on text strings is far less accurate than on key numbers. The textstring may even not be unique.

Any tips to spare?

thanks,

Wim Donckers

[swuehl]

You can use something like

sum({$<Rubriek={"=1320"}>}[Sales for the Period_SALES])

[rwunderlich · Accepted solution]

Using just the "=" does not work for me. I believe you have to write it like this:

sum({$<Rubriek={"=Rubriek=1320"}>}[Sales for the Period_SALES])

-Rob

[swuehl]

You're right, Rob.

[wdonckers]

That works indeed Rob!

Thanks.

What seemed a good idea (having just one field instead of two) does result in rather ugly set statements. Just because set comparison always compares against the text value a dual field.

What I will do is load RubriekID as an additional normal key field  and that will make set statements a lot easier e.g. sum({$<RubriekID={1320}>}[Sales for the Period_SALES])

Thanks again.