aggr on missing data

[surajdhall]

Hi All,

In My trend column the background doesn't appear when there is no data in the first month. But the normal trend gives correct result.

Please help

[sunny_talwar · Accepted solution]

Add NODISTINCT

=Aggr(NODISTINCT

If(FirstSortedValue(TOTAL <Product, Status> Aggr(sum(Margin)/sum(Sales), Product, Status, MonthYear), Aggr(MonthYear, Product, Status, MonthYear)) >

FirstSortedValue(TOTAL <Product, Status> Aggr(sum(Margin)/sum(Sales), Product, Status, MonthYear), -Aggr(MonthYear, Product, Status, MonthYear)),

rgb(179,217,255),

If(Sum(TOTAL <Product, Status>

    Aggr(If(Len(Trim(Below(Sum(Margin)/Sum(Sales)))) > 0 and Len(Trim(Sum(Margin)/Sum(Sales))) > 0,

          If(Round(Alt(Below(Sum(Margin)/Sum(Sales)), 0), 0.0001) < Round(Alt(Sum(Margin)/Sum(Sales), 0), 0.0001), 1, 0)),

    Product, Status, MonthYear)

      ) = 0, Green(),

If(Sum(TOTAL <Product, Status>

    Aggr(If(Len(Trim(Below(Sum(Margin)/Sum(Sales)))) > 0 and Len(Trim(Sum(Margin)/Sum(Sales))) > 0,

          If(Round(Alt(Below(Sum(Margin)/Sum(Sales)), 0), 0.0001) < Round(Alt(Sum(Margin)/Sum(Sales), 0), 0.0001), 1, 0)),

  Product, Status, MonthYear)

      ) = 1, Yellow(),

Brown()))), Product, Status)

[surajdhall]

Thank you so much. This fixed the bug:)