topic Re: Count with Mode function in QlikView

Count with Mode function

Sun, 27 Apr 2014 05:54:25 GMT

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

Re: Count with Mode function

sundarakumar — Sun, 27 Apr 2014 06:30:28 GMT

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

sundarakumar — Sun, 27 Apr 2014 06:32:22 GMT

Pls post some sample data. I think

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

This would fulfill ur need.

-Sundar

Re: Count with Mode function

Sun, 27 Apr 2014 06:55:42 GMT

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

lironbaram — Sun, 27 Apr 2014 07:14:38 GMT

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: Count with Mode function

sundarakumar — Sun, 27 Apr 2014 07:45:33 GMT

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

Sun, 27 Apr 2014 09:09:55 GMT

Hi Sundar

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

Regards

Mohamed Abubakker

Re: Count with Mode function

sundarakumar — Sun, 27 Apr 2014 09:29:55 GMT

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: Re: Count with Mode function

Sun, 27 Apr 2014 10:05:48 GMT

Like this?

Re: Count with Mode function

Sun, 27 Apr 2014 10:16:55 GMT

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

Sun, 27 Apr 2014 10:20:39 GMT

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: Count with Mode function

Sun, 27 Apr 2014 10:51:15 GMT

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

Re: Re: Count with Mode function

Sun, 27 Apr 2014 11:07:23 GMT

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

Sun, 27 Apr 2014 11:18:45 GMT

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.

Re: Re: Re: Count with Mode function

lironbaram — Sun, 27 Apr 2014 11:23:58 GMT

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: Count with Mode function

Sun, 27 Apr 2014 11:37:44 GMT

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

Re: Count with Mode function

Sun, 27 Apr 2014 11:41:25 GMT

Hi Darek

You are correct. Please provide the expression for this.

Thank you

Re: Re: Re: Count with Mode function

Sun, 27 Apr 2014 11:42:03 GMT

Hi I couldn't find any attachment.

Re: Count with Mode function

Sun, 27 Apr 2014 11:53:20 GMT

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>

Re: Count with Mode function

Sun, 27 Apr 2014 11:59:22 GMT

Thank you Darek

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

Regards

Mohamed Abubakker