overall count using aggr

[surajdhall]

Hi All,

I want to calculate overall trend for all the months by each product. Please look at the trend column in the attachment.

I am not sure how to pick min and max month for each product and write it in aggr()

-While calculating overall trend the First month(here Jan) and Last month(here May) is important. ie. Whenever the Last Month is lesser than First month the trend should be called BAD TREND.

Rest of the Trend conditions--

GOOD TREND-- Only Up or Horizontal arrows(ie. every month is greater or equal than previous month)

POSITIVE TREND-- Down arrow is not more than once.

AVERAGE TREND--Down arrow is more than once.

BAD TREND-- First Month(ie. Jan) is greater than Last Month(ie. May)

Eg-

As per the data in attached app, i expect the products to have the mentioned trends-

Product A- both High and Low should be GOOD TREND

Product B- both High and Low should be POSITIVE TREND

Product C and D- both High and Low should be BAD TREND

Please Note-Both up and horizontal arrows is considered UP for trend calculation.

Thanks!

[sunny_talwar · Accepted solution]

Try this

=Aggr(

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)), 'Bad Trend',

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)),  //both up and horizontal arrows is considered UP for trend calculation.

    Product, Status, MonthYear)

      ) = 0, 'Good Trend',

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, 'Positive Trend',

'Average Trend'))), Product, Status)

[surajdhall]

Hi Sunny,

Thank you so much again.

The have done slight changes to inline values (in the attached qvw)and notice that when arrows are horizontal it is taking as BAD Trend.

eg-

Product A- Low is now showing Bad Trend when all the arrows are horizontal. As i mentioned above i want both UP and horizontal arrows to be considered as UP for overall trend calculation. So i expect this row to be GOOD TREND.

Also i wanted to do carry forward of percentage values when a particular of month has no data.

eg- Product D- Low has no data in May, so carry forward the value April (ie. value 0.99%)

eg- Product B- High has no data in Mar, so carry forward the value Feb (ie. value 0.01%)

Please suggest.

[surajdhall]

Thanks Sunny.

I slightly modified expression in the  firstsortedvalue(ie from >= to >) in the condition and it is working as expected.

Thanks you so much for your help.