Count with Mode function

[#1]

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.

Please help.

Regards

Mohamed Abubakker

[#2]

Hi

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

[#3]

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

what would be your result?

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

[#4]

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.

[#5]

Darek

Thank you for the support.

Please consider the following 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

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.

[lironbaram · Accepted solution]

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

[#7]

Mohamed,

So in fact you would like for each item count project number in which this

item has modal quantity...

For this data:

Project, Item, quantity

P1,I1,1

P1,I1,1

P1,I2,1

P2,I1,2

P2,I2,1

You expect result:

I1 - 2

I2 - 2

Am i right?

I have only phone now, when i will back home, i will try to help more

27-04-2014 13:07 użytkownik "Mohamed Abubakker" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Re: Count with Mode

function

reply from Mohamed Abubakker<http://community.qlik.com/people/mabubakker?et=watches.email.thread>in

Scripting - View the full discussion<http://community.qlik.com/message/516844?et=watches.email.thread#516844>

[#8]

Hi Darek

You are correct. Please provide the expression for this.

Thank you

[#9]

Hi I couldn't find any attachment.

[#10]

I will back home in about 2 hours and then i will try.

You must remember, that there is small problem with mode. In case, where

more than one sum(quantity) will have max occurance, mode will return null

For example in case:

P1,I1,2

P2,I1,2

P3,I1,3

P4,I1,3

mode for I1 will not give you result.

Check in help and think which quantity Should survive in this case....

27-04-2014 13:42 użytkownik "Mohamed Abubakker" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Count with Mode

function

reply from Mohamed Abubakker<http://community.qlik.com/people/mabubakker?et=watches.email.thread>in

Scripting - View the full discussion<http://community.qlik.com/message/516866?et=watches.email.thread#516866>

[#11]

Thank you Darek

I know this issue, and to avoid this Iam using isnull function.

Regards

Mohamed Abubakker