Expression as Set Analysis Modifier

[#1]

Hi,

I am trying to produce a Count of All PO Numbers that have a spend of less than $5000. Each PO can have multiple Line Amounts and hence I need to ensure that sum of all Line Amounts for a PO total to less than $5000.

I tried the below formula but failed. I will appreciate any help.

Count({$<(aggr(Sum([Line Amount]),[PO Number])={"<5000"})>}[PO Number])

[Anonymous]

Hello.

Please, try this expression:

Count(Distinct Aggr(If(Sum([Line Amount]) < 5000, [PO Number]), [PO Number]))

Regards.

[#3]

hi try this:

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

Best Regards.

[#4]

I tried but this did not seem to work.

[#5]

This did work, however it produces incorrect values when user selections are made because it is not enabled for user selected data. Could you guide how can I ensure user selections are considered?

Thank you!

[Anonymous]

Hi.

For what I can see, both my solution and Oswaldo's give the same results (besides the Distinct in the Count). Can you provide a sample qvw so we can better help you?

[#7]

Please see attached a sample qvw file I created.

[Anonymous]

Deepinder,

My expression,

Count(Distinct Aggr(If(Sum([Line Amount]) < 5000, [PO Number]), [PO Number]))

is returning what I think is the correct value. Oswaldo's will also work with 2 simple corrections:

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

Both will return the count of distinct PO Numbers that amount to less than $5000.

See the attached qvw. I have selected the PO Numbers with less than $5000. Notice the matching count of 36. If you clear the selection will see that our expressions still count 36.

Regards.

[#9]

This worked great! Thank you guys...

Just a quick follow-up question. How can I apply the same concept if I were to try to find Count of PO's with spend between the range $5000 and $10000.

Thank you!

[Anonymous]

Hello, Deepinger.

It would be like this:

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

or

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

Best Regards.