topic Count if Sum(x)> 5000 in QlikView

Count if Sum(x)> 5000

Mon, 08 Sep 2014 19:21:07 GMT

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.

Re: Count if Sum(x)> 5000

NickHoff — Mon, 08 Sep 2014 19:33:02 GMT

Try set analysis:

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

Re: Count if Sum(x)> 5000

Mon, 08 Sep 2014 19:36:24 GMT

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!

Re: Count if Sum(x)> 5000

maxgro — Mon, 08 Sep 2014 19:37:23 GMT

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

Re: Count if Sum(x)> 5000

NickHoff — Mon, 08 Sep 2014 19:42:46 GMT

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

Re: Count if Sum(x)> 5000

JonnyPoole — Mon, 08 Sep 2014 20:08:25 GMT

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

Re: Count if Sum(x)> 5000

MarcoWedel — Mon, 08 Sep 2014 22:35:30 GMT

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

Re: Count if Sum(x)> 5000

Thu, 23 Oct 2014 20:05:09 GMT

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.@

Re: Count if Sum(x)> 5000

MarcoWedel — Thu, 23 Oct 2014 21:49:00 GMT

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

Re: Count if Sum(x)> 5000

Wed, 29 Oct 2014 19:37:43 GMT

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

Re: Re: Count if Sum(x)> 5000

MarcoWedel — Wed, 29 Oct 2014 23:05:35 GMT

Hi,

your Total Amount for POs having Line Amounts between 5000 and 10000 could be:

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

hope this helps

regards

Marco

Re: Re: Count if Sum(x)> 5000

Tue, 04 Nov 2014 16:38:46 GMT

Marco,

Thank you for the response. Could you kindly share the .qvw for this?

Appreciate your help!

Regards,

Deep

Re: Re: Count if Sum(x)> 5000

MarcoWedel — Tue, 04 Nov 2014 17:08:10 GMT

Hi,

qvw is already attached to my previous post.

regards

Marco

Re: Count if Sum(x)> 5000

simenkg — Tue, 04 Nov 2014 17:10:44 GMT

=Count({<[PO Number]={"=sum([Line Amount])<5000"}>}Distinct [PO Number])

Re: Count if Sum(x)> 5000

Tue, 04 Nov 2014 17:12:37 GMT

Marco,

Thank you!

This worked perfectly!

Appreciate your help!

Regards,

Deep

Re: Re: Count if Sum(x)> 5000

MarcoWedel — Tue, 04 Nov 2014 19:24:44 GMT

You're welcome

Regards

Marco

Re: Count if Sum(x)> 5000

Anonymous — Wed, 06 Apr 2016 09:29:10 GMT

Hello Jonathan,

It was very useful.

thanks