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
];

[prahlad_infy]

@tresesco , Sir , Can i aggregate already aggregated result from firstsorted value function 

[tresesco]

Hi Prahlad,

You can. Looking into your sample app and understanding not much of the actual business, I feel there might be a better /simpler way to deal with the problem. Counting records is tricky. Going with your table : you can use an expression something like:

Counting record (actual table result in your sample) for newest rating less than 5:

=Count(DISTINCT {<Rating={"=FirstSortedValue(Rating, -Aggr(Max(Period), Company, Region, Type, Item))<5"}>} Company)
 

[prahlad_infy]

Tresesco Sir , Thank you for replying .

I tried your expression (for newest rating less than 5)  but its give wrong , even if the Newest rating is 7 yet your expression shows 1 

[prahlad_infy]

I have calculated improvement as well , as one of the expression , which is basically difference between Newest Rating and Rating just prior to Newest FirstSortedValue(Rating,-aggr(max(Period),Company,Region,Type,Item,Period)) - FirstSortedValue(Rating,-aggr(max(Period),Company,Region,Type,Item,Period),2) I wanted to find all those records which have Improvement more than 3 .

[sunny_talwar · Accepted solution]

Can you try this

Count(Aggr(If(If(Period = Max(TOTAL <Company, Region, Type, Item> Period), Rating) < 5, 1), Company, Region, Type, Item, Period))

[prahlad_infy]

Thanking you sunny Sir , this seems to work . But this does not use first sorted value function .

Also , can you help me suggest for rest two 

Q2) Count of records where Improvement is over 3

Q3) Record which observed highest improvement -> Below is example of max improvement .

[sunny_talwar]

I didn't use firstsortedvalue, but the underlying reasoning behind it to count... is that not working...

Looking at Q2 and Q3 now.

[sunny_talwar]

Try this for Q1

Count(Aggr(If(FirstSortedValue(Rating, -Aggr(Max(Period), Company, Region, Type, Item, Period)) < 5, 1), Company, Region, Type, Item))

Q2

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))

Q3 - Where do you want to see this? In the chart or outside of the chart?

[prahlad_infy]

Thank You Sir , both worked like charm . And was nice learning . 

You asked  " Where do you want to see this? In the chart or outside of the chart? " 

- Actually in chart as bar . 

Many thank you