Why does Quick Report with more than 10 measures throw up multiple measures?

[#1]

I have created a quick report object with 19 measure columns. Each measure expression has a conditional expression e.g. =SubStringCount(Concat(_MetricNo, '|'), 19). The challenge is that when I select measure 19, measures 1 and 9 also appear.

Similarly, when I select measure 18, measures 1 and 8 also appear.

Any ideas?

[m_woolf]

You might try enclosing the 19 in apostrophes

[#3]

Hi Richard,

If you expand you substringcount to include your delimiter before and after, each number should be treated separately.

=SubStringCount('|' & Concat(_MetricNo, '|') & '|', '|19|').

Hope that helps

Joe

[#4]

Thanks for the idea, but it does not seem to work

[#5 · Accepted solution]

The expression evaluates to either a 0 or a 1 (as we'd expect a conditional expression to do). My load script was as follows:

CustomerProductMeasures:

LOAD * INLINE [

    _Measures, _MetricNo

    "GSV $", 1

    "GSV (Less Calc Terms) $", 2

    "GSV %", 3

    "Margin Before Promotions", 4

    "# Invoices", 5

    "Avg Invoice Value", 6

    "Total Invoiced Qty", 7

    "Unit GSV $", 8

    "Case Fill %", 9

    "(Under) Ships $", 10

    "Shipped On Time %", 11

    "Late Ships $", 12

    "DIFOT %", 13

    "Std Cost $", 14

    "Std Cost %", 15

    "Unit Std Cost $", 16

    "Units", 17

    "KGs", 18

    "$ To Ship", 19

    ];

I changed my load script to add a 0 (zero) to each of the single numbers so that the load script looks like this:

CustomerProductMeasures:

LOAD * INLINE [

    _Measures, _MetricNo

    "GSV $", 01

    "GSV (Less Calc Terms) $", 02

    "GSV %", 03

    "Margin Before Promotions", 04

    "# Invoices", 05

    "Avg Invoice Value", 06

    "Total Invoiced Qty", 07

    "Unit GSV $", 08

    "Case Fill %", 09

    "(Under) Ships $", 10

    "Shipped On Time %", 11

    "Late Ships $", 12

    "DIFOT %", 13

    "Std Cost $", 14

    "Std Cost %", 15

    "Unit Std Cost $", 16

    "Units", 17

    "KGs", 18

    "$ To Ship", 19

];

I then changed my expressions for numbers 1-9 to read as follows:

=SubStringCount('|' & Concat(_MetricNo, '|'),01)

And it all works i.e. only the columns selected show - unless none selected in which case, all show.