Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
Hello.
Please, try this expression:
Count(Distinct Aggr(If(Sum([Line Amount]) < 5000, [PO Number]), [PO Number]))
Regards.
hi try this:
Count({< [PO Number] = {"=sum([Line Amount])>5000"} >} [PO Number])
Best Regards.
I tried but this did not seem to work.
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!
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?
Please see attached a sample qvw file I created.
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.
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!
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.