topic Re: How can I count the values in an interval dynamically? in App Development

How can I count the values in an interval dynamically?

Christiann — Thu, 08 Apr 2021 08:04:05 GMT

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!!

Re: How can I count the values in an interval dynamically?

Dalton_Ruer — Thu, 08 Apr 2021 12:34:29 GMT

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

Re: How can I count the values in an interval dynamically?

Christiann — Thu, 08 Apr 2021 13:20:15 GMT

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

Re: How can I count the values in an interval dynamically?

Dalton_Ruer — Thu, 08 Apr 2021 16:34:00 GMT

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?

Re: How can I count the values in an interval dynamically?

Christiann — Thu, 08 Apr 2021 18:51:30 GMT

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.

Re: How can I count the values in an interval dynamically?

Christiann — Thu, 08 Apr 2021 19:34:16 GMT

I send you a little example....