SubStringCount(Concat

[hjm35055]

Hi,

I have the following inline tables in my app which are used to conditionally show dimensions in a table. 

In the one table I am using the following formula and everything works fine.

=SubStringCount(Concat(%dimensionID, '|'), 'A')

In the other table I am using this formula and nothing is working.

=SubStringCount(Concat(%refresh_dimensionID, '|'), 'A')

Can anyone please tell me why?  I am not referencing the same fields in both tables, for example City in %dimension is "Physical City" and City in %refresh_dimension is "Customer City".  I tried changing the ID from letters to numbers in %refresh_dimension but it still didn't work.

Dimensions:

LOAD * INLINE [

%dimension, %dimensionID

City, A

State, B

Profit Center, C

Profit Center Name, R

Location, D

Date, E

Day Part, F

Time, G

Menu Item Name, H

Major Group, I

Family Group, J

Tender, K

Weather, L

Temperature, M

Check#, N

Week, O

Month, P

Day, Q

];

Refresh_Dimensions:

LOAD * INLINE [

    %refresh_dimension, %refresh_dimensionID

Market Center, A

Customer, B

Quarter, C

State, D

City, E

Address, F

Year, G

Customer#, H

Customer Contact, I

Invoice#, J

Ticket Date, K

Product Code, L

Product Desc, M

Category, N

UOM, O

Qty Per, P

Tax, Q

Month-Year, R

Rank Sales, S

Rank Units, T

];

[vishsaggi]

rename your %refresh_dimensionID to just %dimensionID and check? IF not can you share an eg of your expected output with some sample data?

[hjm35055]

Here you go!

[vishsaggi · Accepted solution]

Your Calculation condition in your chart properties general tab is pointing to %dimension, it should be %refresh_dimension as shown below change it and also change your substringcount() function to refresh_dimensionID like A, B, C. There are no D, E,F in your refresh_dimensionID field.