Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Christiann
Partner
Partner

How can I count the values in an interval dynamically?

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

5 Replies
Dalton_Ruer
Employee
Employee

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

Christiann
Partner
Partner
Author

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

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?

CycleTime.png

Christiann
Partner
Partner
Author

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
Partner
Partner
Author

I send you a little example....