Showing if count greater than a number

[Anonymous]

Hi again,

I need some advise on how to filter a chart to show only Customer data that has more than count greater than 2.

not sure if this has been asked before or maybe i'm not getting the correct key words.

Example:

ID, Customer, Sales

1, A, 10

2, A, 100

3,B,200

4,B, 200

5,B, 100

6,C, 10

7,D, 10

8, D, 10

9, D, 20

10, D, 20

11, E, 10

12, E, 10

13, E, 10

14, F, 10

Output:

Customer, GP

B =500

D = 60

E = 30

Do i use Aggr funtion to get this output?

I am want to display this in a chart and ideally display only Customer B, D and E in a listbox.

thanks

Derrell

[Kushal_Chawda]

Create the Straight Table

Dimension:

Customer

Expression:

=sum({<Customer ={"=count(Customer )>2"}>}Sales)

or

sum(if(Count(Customer)>2,Amount))

[#3]

Hi Derrell

You can use the following as a calculated dimension:

=aggr(if(Rank(count(Customer)) > 2, Customer), Customer)

It will filter the rows displayed for only the Customers you want to see

[Chanty4u]

u can try this

=Sum(IF(Count(Customer)>2,Amount)

[MK_QSL · Accepted solution]

You can do it by several ways...

Method 1 : Script

Data:

Load * Inline

[

  ID, Customer, Sales

  1, A, 10

  2, A, 100

  3,B,200

  4,B, 200

  5,B, 100

  6,C, 10

  7,D, 10

  8, D, 10

  9, D, 20

  10, D, 20

  11, E, 10

  12, E, 10

  13, E, 10

  14, F, 10

];

Left Join (Data)

Load IF(Count(ID)>2,1,0) as TotalFlag, Customer Resident Data

Group By Customer;

Now create a Straight table/Pivot Table/Chart

Dimension

Customer

Expression

SUM({<TotalFlag = {1}>}Sales)

==============================================================

Method 2 Set Analysis:

Create a Straight table/Pivot Table/Chart

Dimension

Customer

Expression

SUM({<Customer = {"=COUNT(ID)>2"}>}Sales)

or

SUM({<Customer = {"=COUNT(Customer)>2"}>}Sales)

==============================================================

Method 3 : If in expression

Create a Straight table/Pivot Table/Chart

Dimension

Customer

Expression

or

IF(Count(Customer) > 2, SUM(Sales))

/*This will give you wrong total at the top when you do Expression Total in Total Mode of Straight Table */

SUM(Aggr(IF(COUNT(Customer)>2, SUM(Sales)),Customer))

or

SUM(Aggr(IF(COUNT(ID)>2, SUM(Sales)),Customer)) // This will give you correct total

==============================================================

Method 3 : Calculated Dimension

Create a Straight table/Pivot Table/Chart

Dimension

Aggr(IF(COUNT(Customer)>2,Customer),Customer)

or

Aggr(IF(COUNT(ID)>2,Customer),Customer)

Tick Suppress When Value is Null in Dimension Tab for this Calculated Dimension.

Expression

SUM(Sales)

==============================================================

Performance point of view, 2nd method with Set Analysis is the best to use.

Happy Qliking !

[Anonymous]

Thank you all! it works!