Limiting the number of values displayed in a straight table

[#1]

Hi all,

I need to create a list of new clients whose turnover is over 1000 € since the beginning of the year.

So I set up a straight table with CLIENT as dimension and the following expression :

sum(if(aggr(sum({$<YEAR={$(=Max(YEAR-1)}>}TURNOVER), CLIENT, MONTH)=0 and aggr(sum({$<YEAR={$(=Max(YEAR)}>}TURNOVER), CLIENT, MONTH)>=0,aggr(sum({$<YEAR={$(=Max(YEAR))}>}TURNOVER),CLIENT,MONTH),0))

I get the following result:(abrideg)

Abrégé	Acquisitions
	40549 €
BRAMPTO1	3505 €
ATECH2	3458 €
SOGEMA3	3261 €
FABREX1	2844 €
DAUDRUY1	2589 €
MAXEL1	2543 €
LANOLIN1	2453 €
DUFLOT3	1729 €
WESTFAL2	1716 €
COUSINC1	1365 €
GEF1	1196 €
CILTEC1	1150 €
KECHMAR1	971 €
REGENER1	943 €
NEXANS6	895 €

So I tried to find an expression to count the values over 1000 € and then use it in the "Show max values (1-100)' with no succes:

Could you help me ?

Thanks a lot.

Vincent

[Miguel_Angel_Baeyens · Accepted solution]

Hello Vincent,

One simple way is to add that expressions into an if conditional

if(expression > 1000, expression)

not setting anything for else should display what you want.

[#3]

Hello Miguel,

Why did not I think of it ?

This is working great.

Thanks a lot

[#4]

Dear Miguel,

Applying your suggestion I get the following result (this is the total amount of turnover for the first two months of 2010. Without the 1000+ condition, I would have a total of 40733 €.

I tried to apply this formula within a new straight table with MONTH as dimension instead of CUSTOMER. The problem is that it does not retain the condition > 1000 € anymore; and so I get the following result:

As you can see, the breakdown per month does amount to the global turnover, meanwhile I only want the turnover per month for client whose two mont period turnover is over 1000 €.

If you have an idea,that would be great.

Thanks

Vincent