Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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



1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

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

33 Replies
sundarakumar
Valued Contributor II

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

sundarakumar
Valued Contributor II

Re: Count with Mode function

Pls post some sample data. I think

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

This would fulfill ur need.

-Sundar

Not applicable

Re: Count with Mode function

Hi Sundar

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

Consider the following sample 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 - J3

Following expression will return 2

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

I need to know the count of 2.

Thank you.

Mohamed Abubakker

lironbaram
Honored Contributor II

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

sundarakumar
Valued Contributor II

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

Not applicable

Re: Count with Mode function

Hi Sundar

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

Regards

Mohamed Abubakker

sundarakumar
Valued Contributor II

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

Not applicable

Re: Re: Count with Mode function

Like this?

Not applicable

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

Community Browser