- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
aggr on missing data
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much. This fixed the bug:)