topic Re: Adhoc Report and Metrics in QlikView

Adhoc Report and Metrics

tmumaw — Thu, 03 Nov 2016 14:52:33 GMT

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

Re: Adhoc Report and Metrics

mvanlutterveld — Thu, 03 Nov 2016 15:11:20 GMT

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

Re: Adhoc Report and Metrics

tmumaw — Thu, 03 Nov 2016 16:53:08 GMT

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
];

Re: Adhoc Report and Metrics

tmumaw — Thu, 03 Nov 2016 17:47:13 GMT

Changed it to use the names vs numbers. Just did not remember to put them in quotes. Thanks for you help Michiel.

Re: Adhoc Report and Metrics

mvanlutterveld — Fri, 04 Nov 2016 08:33:24 GMT

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'.

Re: Adhoc Report and Metrics

tmumaw — Fri, 04 Nov 2016 10:36:19 GMT

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')

Re: Adhoc Report and Metrics

mvanlutterveld — Fri, 04 Nov 2016 14:54:49 GMT

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|')