How to get aggregated count from the result of the First sorted values ?

[prahlad_infy]

Hi All ,

I have identified Newest and old rating using first sorted value . 

But i am not sure of  getting expression to get below , which is like aggregating data result which i am getting from first sorted value .

Q1) Count of records where Newest Rating is less than 5

Q2) Count of records where Improvement is over 3

Q3) Record which observed highest improvement

Data :

LOAD * INLINE [

Company, Region, Type, Item, Period, Rating
Ab Classics, A, Goods, Furnance Woot, 20171, 3
Ab Classics, A, Goods, Furnance Woot, 20192, 4
Ab Classics, A, Goods, Furnance Woot, 20193, 2
Ab Classics, B, Plastics, Catheter, 20181, 3
Ab Classics, B, Plastics, Catheter, 20172, 17
Ab Classics, C, Plastics, Pipes, 20184, 5
Anura, B, Goods, Long Stands, 20183, 1
Anura, B, Goods, Long Stands, 20184, 8
Anura, D, Goods, Ultra Edge, 20182, 5
Anura, D, Consumables, Pie, 20183, 3
Anura, D, Consumables, Pie, 20191, 7
Anura, A, Consumables, Pie, 20184, 5
Alcatel, B, Electronics, Fibre Strands, 20183, 1
Alcatel, B, Electronics, Fibre Strands, 20184, 8
Alcatel, C, Electronics, C Resistance, 20182, 5
Alcatel, C, Electronics, C Resistance, 20183, 3
Alcatel, C, Liquids, LUBRO, 20191, 7
Alcatel, A, Liquids, Diseo, 20174, 8
Alcatel, A, Liquids, Diseo, 20163, 11
Alcatel, C, Liquids, LUBRO, 20193, 8
];

[sunny_talwar]

Chart as a bar? Would you be able to expand on this?

[prahlad_infy]

What I meant was , if Q3 gives , 7 as output value , i wish to show 7 as bar height . 

[sunny_talwar]

In a straight table you want a bar? or do you need a separate bar chart with a single bar?

[prahlad_infy]

Actually separate bar chart with a single bar . 

[sunny_talwar]

This?

Max(Aggr(
  
  Count(Aggr(
    
    If((FirstSortedValue(Rating, -Aggr(Max(Period), Company, Region, Type, Item, Period), 2) - FirstSortedValue(Rating,-Aggr(Max(Period), Company, Region, Type, Item, Period))) > 3, 1)
  
  , Company, Region, Type, Item))

, Company, Region, Type, Item))