How can I count the values in an interval dynamically?

[Christiann]

Hi all,

I have a lot of values called CYCLETIME for serval ARTICLES and HOURS.

First the CYCLETIME should be separated in intervals with different widths (0.01 / 0.02 / 0.05 / 0.1 and 0.2) and then the CYCLETIME should be added in the intervals.

I'm searching for the count of CYCLETIME  from the biggest interval (not the interval himself) in the different step widths maybe like this:

ARTICLE    RANGE   COUNT

ABC            0.01           87

                     0.02          89

                     0.05          95

                     0.1           115

                     0.2           230

I need this in a straight or pivot table because if I select different hours I have different results and I must calculated with this values.

Any good idea for me?

Thanks for your effort!!

[Dalton_Ruer]

Can you elaborate a little with some fake data as to what it is you are looking to see. 

[Christiann]

Hi Dalton_Ruer,

You can use the following short script to generate fake data:

DATA:

LOAD

   chr( 64 + ceil( rand() * 26 ) ) AS ARTICLE,

   ceil( rand() * 100 ) + rand() AS CYCLETIME

AUTOGENERATE( 1000 );

RANGE:

LOAD * INLINE [

   RANGE

   0.01

   0.02

   0.05

   0.1

   0.2 ] (delimiter is '|' );

What I'm searching for is a pivot table with ARTICLE and RANGE as dimensions and a function like this:

count( aggr( mode( aggr( count( ceil( CYCLETIME, RANGE ) ), RANGE, ARTIKEL ) ), RANGE, ARTIKEL ) )

but this function doesn't work.

I hope this helps for understanding.

Christian

[Dalton_Ruer]

When I run the script I get the following data model. 

What are you hoping to see for the row with R where it's cycletime is 60.982xxxx?

[Christiann]

This is correct.

Now you can create intervals in the 5 different steps size (the content from field RANGE) and order the CYCLETIME into these intervals. For your first line:

For the interval with step size 0.01 the value 60.98275 is in the interval 60.98 <= x < 60.99

for the next interval with step size 0.02 this value is in the interval 60.98 <= x < 61.00

with step size 0.05 the interval 60.95 <= x < 61.00 etc. etc. Every CYCLETIME must be sorted in an interval with every step size.

After this you must count the content from every interval step and find for every RANGE the interval with the most CYCLETIMEs.

You can use the class -function like

class( CYCLETIME, RANGE ) and in a pivot table with RANGE as dimension you have the result for every step size.

Because I don't need the interval with the most CYCLETIME but only the count of CYCLETIME in this interval I use the ceil - function for the upper fence from the interval (it's the same if you count the interval or the upper fence)

My function from top:

ceil( CYCLETIME, RANGE )

then I count the intervals

count( ceil( CYCLETIME, RANGE ) )

the mode - function deliver the max of count:

mode( count( ceil( CYCLETIME, RANGE ) ) ) but count in mode must be aggregated

mode( aggr( count( ceil( CYCLETIME, RANGE ) ), RANGE, ARTICLE ) )

This was my plan but doesn't work and now I'm searching a new idea...

The result should be like the table from top

first column the ARTICLE next the RANGE (for every ARTICLE) and then the max count from CYCLETIME for every step size of intervals.

[Christiann]

I send you a little example....