Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Please help.

Regards

Mohamed Abubakker



33 Replies
Not applicable
Author

Hi

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Darek

Thank you for the support.

Please consider the following data

   

Item ProjectQuantity Ordered
Item - AProject - A1
Item - AProject - B1
Item - AProject - B1
Item - AProject - D2
Item - AProject - E3
Item - AProject - F4
Item - AProject - G2
Item - AProject - H2
Item - AProject - I2
Item - AProject - 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
Partner - Master III
Partner - Master III

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

Not applicable
Author

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>

Not applicable
Author

Hi Darek

You are correct. Please provide the expression for this.

Thank you

Not applicable
Author

Hi I couldn't find any attachment.

Not applicable
Author

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>

Not applicable
Author

Thank you Darek

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

Regards

Mohamed Abubakker