Logic issue in the Straight table

[#1]

Hi All,

I have a straight table , calculating spend and MinSpend  KPI's. If Spend is   greater than MinSpend, those count of Resident names has to show in a Textbox.In the below example,i have 17 Resident names having spend > MinSpend.

What is the logic to show these counts in a text box.  .KPI Logics are mentioned below.

Spend Logic: =if(sum(TotalPaid)>MinSpend,(sum(TotalPaid)),0)

MinSpend Logic:

411.15*count({<Community={'Holly Creek'}>}DISTINCT(Month))*count({<Community={'Holly Creek'}>}Distinct(Year))*count({<Community={'Holly Creek'}>}distinct(FirstName&LastName))+

9*count({<Community={'Someren Glen'}>}Distinct(ServDate))*count({<Community={'Someren Glen'}>}distinct(FirstName&LastName))+

268.83*count({<Community={'Clermont Park'}>}DISTINCT(Month))*count({<Community={'Clermont Park'}>}Distinct(Year))*count({<Community={'Clermont Park'}>}distinct(FirstName&LastName))

Thanks,

Krishna

[swuehl · Accepted solution]

Maybe like

=Sum( Aggr(

     If( Sum(TotalPaid) >

411.15*count({<Community={'Holly Creek'}>}DISTINCT(Month))*count({<Community={'Holly Creek'}>}Distinct(Year))*count({<Community={'Holly Creek'}>}distinct(FirstName&LastName))+

9*count({<Community={'Someren Glen'}>}Distinct(ServDate))*count({<Community={'Someren Glen'}>}distinct(FirstName&LastName))+

268.83*count({<Community={'Clermont Park'}>}DISTINCT(Month))*count({<Community={'Clermont Park'}>}Distinct(Year))*count({<Community={'Clermont Park'}>}distinct(FirstName&LastName)), 1,0)

, FirstName, LastName))

[#3]

Thanks Stefan,

Its working.