7 Replies Latest reply: Jul 26, 2012 8:15 AM by Benjamin Dykstra RSS

    Average Aggregation Gauge Issue

      Hello:

       

      I am having an issue with one of my gauges--if two years are not selected when trying to compare previous years, the expression returns null. It works fine comparing the current and previous year (2012 vs 2011). While the expession works if you select both of the years you want to compare, this is not ideal.

       

      An additional note: the other gauges on this sheet work fine when comparing previous years; however, none of the other gauges are using Avg(Aggr(

       

      If(Max(CCYTD)=1,

      ($(vCCYTDOnHandDol) - $(vPCYTDOnHandDol) / $(vPCYTDOnHandDol),

       

       

      ($(vMaxCYOnHandDol) - $(vMax-1CYOnHandDol) / $(vMax-1CYOnHandDol)

       

       

       

       

      CCYTD is a flag for the current calendar year to date. PCYTD is a flag for the previous calendar year to date.

       

      vCCYTDOnHandDol: Avg(Aggr(Sum({$<CCYTD={1}>}OnHandDol),POSDate))

      vPCYTDOnHandDol: Avg(Aggr(Sum({$<PCYTD={1}>}OnHandDol),POSDate))

      vMaxCYOnHandDol: Avg(Aggr(Sum({$<CYYearNum={$(=Max(Total CYYearNum))}>}OnHandDol),POSDate))

      vMax-1CYOnHandDol: Avg(Aggr(Sum({$<CYYearNum={$(=Max(Total CYYearNum-1))}>}OnHandDol),POSDate))

       

      Is there any way to fix this expression? Any help would be greatly appreciated. See attachments for a look at the gauges.