Aggr Help!

[#1]

Hi

I have the following formula which allows me to show if a KPI is Red/Amber/Green in a guage -  based on;

<1 of the metrics in the KPI group is a Challenge = Green,

>1 metric Challenge = Red,

and 1metric Challenge= Amber

if

(sum(aggr(if(SummaryActivityDate=date(max(total <MetricKPI> SummaryActivityDate)),sum(Challenges)),MetricKPI,SummaryActivityDate))<1,0.825,
if(sum(aggr(if(SummaryActivityDate=date(max(total <MetricKPI> SummaryActivityDate)),sum(Challenges)),MetricKPI,SummaryActivityDate))>1,0.165,
if(sum(aggr(if(SummaryActivityDate=date(max(total <MetricKPI> SummaryActivityDate)),sum(Challenges)),MetricKPI,SummaryActivityDate))=1,0.5,0.825)))
)

I am now needing this formula amending so I can run a mini bar chart with the red  amber green (where red =1, Amber = 2 and Green = 3) for a 15 month period but Im struggling to do this.

The data appears to roll up to KPI level or Month level so it isnt correct pls help

[Anonymous]

Hi joanne,

             Can you please explain briefly with example or any images,i am not getting your question.

Thanks

[#3]

Please post sample qvw.