Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Having a little issue with the metric selection for my Adhoc report. I have the following:
MetricsOptions:
LOAD * INLINE [_metricsNo, _metrics
1, LID
2, Fuel
3, R&M
4, Total Costs
5, Billed Hrs
6, Std Hrs
7, Util %
8, No Units
9, Avg Cost
10, Billed
];
When I select billed I am also getting LID. Here are my parameters for both:
=SubStringCount(Concat( _metricsNo, '|'),1) <----- LID
=SubStringCount(Concat( _metricsNo, '|'), 10) <---- Billed
Any ideas?
Thanks
Hi Thom,
You get LID when selecting Billed because _metricsNo 1 (LID) is part of 10 (Billed). By adding a preceding zero to the _metricsNo values 1-9 this problem is solved. An alternative solution is to use the _metrics field in your SubStringCount instead of _metricsNo.
Cheers,
Michiel
Hi Thom,
You get LID when selecting Billed because _metricsNo 1 (LID) is part of 10 (Billed). By adding a preceding zero to the _metricsNo values 1-9 this problem is solved. An alternative solution is to use the _metrics field in your SubStringCount instead of _metricsNo.
Cheers,
Michiel
I changed it to this with no luck.......
MetricsOptions:
LOAD * INLINE [_metricsNo, _metrics
10, LID
20, Fuel
30, R&M
40, Total Costs
50, Billed Hrs
60, Std Hrs
70, Util %
80, No Units
90, Avg Cost
100, Billed
];
Changed it to use the names vs numbers. Just did not remember to put them in quotes. Thanks for you help Michiel.
Glad to help. I prefer using descriptive names instead of numbers. It's a bit more work at first, but they are much easier to remember. There comes a time sooner or later when you ask yourself 'what does 7 mean'.
Michiel,
Another question for you why would both R&M and % R&M come out together when I only select % R&M ? I am working off the _metrics.
MetricsOptions:
LOAD * INLINE [_metricsNo, _metrics
1, LID,
2, Fuel
3, R&M
4, Total Costs
5, Billed Hrs
6, Std Hrs
7, Util %
8, No Units
9, Avg Cost
10, Billed
11, % R&M
12, % Fuel
];
=SubStringCount(Concat(_metrics, '|'),'R&M')
=SubStringCount(Concat(_metrics, '|'), '% R&M')
Hi Thom,
Because R&M is part of both 'R&M' and '% R&M'. You should expect the same results for 'Billed Hrs' and 'Billed'. This can solved by making the result of the substringcount more unique. I've added some code that I've used in an adhoc report.
Prod_SMD_Table_Dim_detail:
LOAD * Inline [
_Prod_SMD_Dim_detail
Analysis
Analysis Component
CRT Code
CRT Description
CRT Number
Sample
Sampled Date
Sampled Time
Sampled DateTime
Manuf. Date
Manuf. Time
Manuf. DateTime
Product
Product Name
Sampling Point
Shift
SKU
Source Maker
];
The substringcount for the 'Sample' column: SubStringCount('|' & Concat(distinct _Prod_SMD_Dim_detail, '|') & '|', 'Sample|')