Please help explain this SubStringCount and Concat function expression :)

[jblomqvist]

Hello

I have the following Dimensions and Expressions:

Measures:

LOAD * INLINE [

    _MetricsNo, _Metrics

    1, Sales

    2, Margin

    3, Margin %

    4, Number of Orders

    5, Average Discount

];

Dimensions:

LOAD * INLINE [

    _Dimension

    Category

    Company

    Country

    Division

    Sales Person

    Quarter

    Month Year

    Year

];

At the front end I am using the following for a Calculation Condition for the Dimension:

SubStringCount(Concat(_Dimension,'|'), 'Category')>0

Can anyone explain why there is a need for '|' and >0 in the above expression?

If I take away the >0 it seems to work fine so not sure why it is there.

And similar for an Expression:

SubStringCount(Concat(_MetricsNo, '|'), 1)

Why there is a need for '|' ?

I think the '|' is used as seperators but not really sure why it's needed.

I have attached an example for you to show this in the straight table.

Thanks

[Clever_Anjos]

The '|' is there to avoid misinterpretation of substrings, for example

"Category1", "Category2" will not be considered since the concatenation would transform into "Category1|Category2"

>0 its a way of saying "More than one occurance"

[jblomqvist]

Hi Clever,

Thanks for your reply Can you please elaborate a bit more on the two I almost understand but not fully.

You are saying without the '|' it would not work properly?

And what do you mean by more than one occurance?

Thanks

[Clever_Anjos]

You are saying without the '|' it would not work properly?

In this application it will work, but using | is kind of a 'standard recipe', the person who elaborate the expression maybe saw this expression somewhere else

And what do you mean by more than one occurance?

If you have "Category" selected (or nothing is selected at all) , SubStringCount would return 1, if its not selected this will return 0.

Same remark about using a 'standard recipe'

Please add SubStringCount(Concat(_Dimension,'|'), 'Category') and Concat(_Dimension,'|') to textboxes and play with the listbox paying attention to the values returned

[Clever_Anjos]

"Please add SubStringCount(Concat(_Dimension,'|'), 'Category') and Concat(_Dimension,'|') to textboxes and play with the listbox paying attention to the values returned"

Sorry, you already have done that