33 Replies Latest reply: Apr 28, 2014 12:35 AM by Mohamed Abubakker

# Count with Mode function

Hi

I am using the mode function to find out the item which was ordered many times with the same quantity during the project life cycle.

The expression that I used to compute the mode is

= mode  (aggr (sum(Quantity), Project, Item))

I need the count of mode in the aggregate output.

I am using this the above expression in the chart and I need to know how much percentage that the same quantity is used in the project.

Regards

Mohamed Abubakker

• ###### Re: Count with Mode function

The expression that u have created will return only one value.

What i understand is

u want to know which item was ordered with the same quantity number, very often.for each project.

Pls correct me if am wrong.

-Sundar

• ###### Re: Count with Mode function

Hi Sundar

I am using a chart function with the Item as dimention,

Consider the following sample data

 Item Project Quantity Ordered Item - A Project - A 1 Item - A Project - B 1 Item - A Project - B 1 Item - A Project - D 2 Item - A Project - E 3 Item - A Project - F 4 Item - A Project - G 2 Item - A Project - H 2 Item - A Project - I 2 Item - A Project - J 3

Following expression will return 2

= mode  (aggr (sum(Quantity), Project, Item))

I need to know the count of 2.

Thank you.

Mohamed Abubakker

• ###### Re: Re: Count with Mode function

hi

attach is an example

but as the example uses a variable

it only can check for the most common value in all the data

• ###### Re: Re: Count with Mode function

Hi

Is there a option to store the Mode value in the variable and use that variable in the another expression in the table?

• ###### Re: Re: Re: Count with Mode function

Text box should return 5.

• ###### Re: Re: Count with Mode function

Hi

The text box should return 5 because in Project-B total of two quantities has been issued.

• ###### Re: Count with Mode function

Pls correct me if am wrong.

u want to know the number of times the order was made with the same quantity for a particular item.

The particular item would be the item that was ordered the most for each project.

-Sundar

• ###### Re: Count with Mode function

Hi Sundar

You are correct. I am interested on same quantity count.

Regards

Mohamed Abubakker

• ###### Re: Count with Mode function

This can be done at the script level.

1. Load an extra field along with the table with value 1 in it.

2. Do a resident load and use group be. Group by Item,Project , Quantity and sum(count) as the aggregate function in the resident load. This will result in the number of times a same order is made in the same qty.

3. now use aggregate in the front end like

aggr(max(count),item, Project){u would not need to use quantity here in this aggregate}

Hope this helps.

-Sundar

• ###### Re: Count with Mode function

Hi Sundar

I cannot load extra table since the record size is more than 10 million. Please suggest any other option.

Regards

Mohamed Abubakker

• ###### Re: Re: Count with Mode function

Mohamed,

i have few questions:

1. if you have data like this:

project, item, quantity

P1,I1,5

P1,I2,5

P1,I3,4

P2,I1,4

P2,I2,4

Another way - shoul your mode be calculated on all data level (and it will be 4) or rather on project level (and it will be 5 for P1 and 4 for P2)?

2. You use expression sum(quantity)

Is it possible, that you will have more than 1 row for project and item,

for example:

project, item, quantity

P1,I1,2

P1,I1,3

P1,I2,2

P1,I3,5

if it is possible, you would like here to have 5 sa this mode value?

regards

Darek

• ###### Re: Re: Count with Mode function

Darek,

Our objective is to identify the standard quantity of each Item for a project.

So, For each project, I compute the total quantity by Item using the following expression

= aggr (sum(quantity), Project, Item)

Then, in order to get the standard quantity for an item, which is the most common quantity, I used mode function

= mode (aggr (sum(quantity), Project, Item))

I got the most common quanity for each item.

I need to know, for how many projects this standard quantity is used for an Item. I need this information, because, If it is used less than 50 % of the total projects than I should not consider as Standard quantity for this item.

• ###### Re: Re: Count with Mode function

Like this?

• ###### Re: Re: Count with Mode function

Darek

Thank you for the support.

 Item Project Quantity Ordered Item - A Project - A 1 Item - A Project - B 1 Item - A Project - B 1 Item - A Project - D 2 Item - A Project - E 3 Item - A Project - F 4 Item - A Project - G 2 Item - A Project - H 2 Item - A Project - I 2 Item - A Project - J 3

count({<Quantity={\$(= mode (aggr (sum(Quantity), Project, Item)))}>}Quantity)

Expression - mode (aggr (sum(Quantity), Project, Item)) will return 2

and the count should return 5, your expression will return only 4, it will not include Project - B, since the same item issued two times with quantity 1.

• ###### Re: Re: Re: Count with Mode function

hi

have a look at the attach file

the text object calculate what you need

try this ,

but any way

the variable has the expression

=mode  (aggr (sum([Quantity Ordered]), Project, Item))

and the counter has this expression

= sum(aggr(if(sum( [Quantity Ordered])= vMode,1,0),Item,Project))

• ###### Re: Re: Re: Count with Mode function

Hi I couldn't find any attachment.

• ###### Re: Re: Re: Count with Mode function

Thank you Liron. It works perfect.

• ###### Re: Re: Re: Re: Count with Mode function

Mohamed,

as i understood, you need to have results on Item level (in chart with Item as dimension).

If yes, i'am afraid, Lirons expression may not work as you expect.

I loaded one more Item (Item B) and put Item as dim and expression from Liron as expression in this chart ...

Please, let me know if you still would like to find solution for chart with Item as dimension.

regards

Darek

• ###### Re: Count with Mode function

Hi Darek

You are correct. It is not working for more than one Item.

• ###### Re: Re: Count with Mode function

Mohamed,

this is solution i was looking for

For each Item number of projects with sum(quantity) having biggest occurance.

Result chart is with green background.

You can find also tablebox with loaded data, listboxes allowing to make some selections, 2 additional straight tables with additional rows and columns.

regards

Darek

• ###### Re: Count with Mode function

Pls post some sample data. I think

aggr(mode(item),project,quantity).

This would fulfill ur need.

-Sundar

• ###### Re: Count with Mode function

Hi Liron