cancel
Showing results for
Did you mean:
Creator II

## How to get a count of a field for a Pie Chart with specific criteria

I am trying to get a count of IDs that meet a specific set of criteria for a Pie chart.  The criteria is:

Sum(Charges) < 5 and Sum(ChargeAmount) < 1000 and FY = vCurrent

I need a Count of the GID field when this criteria is met.

1 Solution

Accepted Solutions
MVP

Try this

=Sum(Aggr(If(sum(if(FY=vFY_Current and [PCard Deliquency Flag]<>'x',[PCard No of Charges])) > 0 and Sum([PCard No of Charges]) < 5 and Sum([PCard Charge Amount]) < 1000, 1), [PCard Full Name]))

7 Replies
Anonymous
Not applicable

try

sum(aggr(if(Sum(Charges) < 5 and Sum(ChargeAmount) < 1000 and FY = vCurrent,1),GID))

try smthg like -

Count({<Charges = {"=Sum(Charges)>5"},FY={\$(=vCurrent)},ChargeAmount={"=Sum(ChargeAmount) < 1000"}>}GID)

Creator II
Author

This is very close, but I also need to change the criteria on Charges.  I need where it is greater than 0 but less than 5.

Anonymous
Not applicable

like that?:

sum(aggr(if(Sum(Charges) < 5 and Sum(Charges) >0 and Sum(ChargeAmount) < 1000 and FY = vCurrent,1),GID))

Creator II
Author

That should work, but for some reason I am still getting undesired results.  I'm not sure if it is an underlying data issue or not.  I've attached a copy of the QVW if anyone wants to investigate.  The goal is to get a Count of the names in the Employees Below Threshold table.

MVP

Try this

=Sum(Aggr(If(sum(if(FY=vFY_Current and [PCard Deliquency Flag]<>'x',[PCard No of Charges])) > 0 and Sum([PCard No of Charges]) < 5 and Sum([PCard Charge Amount]) < 1000, 1), [PCard Full Name]))

Creator II
Author

That did it...thanks!!!

Community Browser