Count if Sum(x)> 5000

[#1]

Hi,

I am trying to write an expression where I want to calculate the number of distinct PO's with Line Amount less than or equal to $5000.

I believe I need to Aggregate the [Line Amount] spend by [PO Number] to list all such PO's in straight table.

I'd appreciate if I could get the best method/syntax to produce this result?

Thank you in advance.

[NickHoff]

Try set analysis:

=COUNT({$<LineAmount <= {'5000'}>}DISTINCT PO)

[#3]

Nick, thanks for the response. Maybe I wasnt clear! Each PO can have multiple Line Amounts against it.

I need to sum all Line Amounts for a PO number.

And then apply the <=5000 condition to count all PO's with spend greater than 5000.

Hope this helps.

Thank you!

[maxgro]

count( {$<LineAmount = {“<=5000”}>} distinct PO)

[NickHoff]

=SUM(AGGR(DISTINCT PO)({$<LineAmounts = {'<=5000'}>}LineAmounts)

[JonnyPoole]

I think this would work where [PO] is your field that denotes a unique PO. 

count( DISTINCT  if (  aggr(sum([Line Amount) , [PO] ) > 5000, [PO]))

[MarcoWedel]

Hi,

one possible solution:

=-Sum(Aggr(Sum([Line Amount]), [PO Number])<=5000)

tabLineAmounts:
LOAD Chr(64+RecNo()) as [PO Number],
     Ceil(Rand()*100) as [Line Amount]
AutoGenerate 10
While IterNo() <= 98;

hope this helps

regards

Marco

[#8]

Marco,

Thank you for the response. Could you also guide me how might I able to do the same thing for a range.

Example: Count of all PO's where Sum(Line Amount) is greater than $5000 and less than/equal to $10,000.

Thank you and appreciate your help.@

[MarcoWedel · Accepted solution]

=-Sum(Aggr(Sum([Line Amount]), [PO Number])>5000 and Aggr(Sum([Line Amount]), [PO Number])<=10000)

please mark helpful / correct whatever answer helped you solve your problem.

thanks

hope this helps

regards

Marco

[#10]

Marco,

Thank you for your help! My data does not seem to tie... Can you help me with below:

1) My numbers are not tying up. Is there a way I can modify the set expression to exclude all records where [PO Number] is blank?

2)Can you also help me with finding the total dollar value of all such PO's.

Example: I want to find the total dollar value of the 5 PO's that have spend between 5000 and 10000

Thank you for your help!

Regards,

Deep