Max of the count ?

[poluvidyasagar]

Hi All,

I am trying to achieve the following:

Area	Sub Area	Date	Count
A	A1	6/4/2018	10
A	A2	6/5/2018	15
A	A1	6/6/2018	20
A	A2	6/7/2018	30
B	B1	6/4/2018	10
B	B2	6/5/2018	35
B	B1	6/6/2018	50
B	B2	6/7/2018	20

Here is my data set and i want to get max of count for each sub area under each area:

my result should look like this:

Area	Sub Area	Max
A	A1	20
A	A2	30
B	B2	35
B	B1	50

What is the best expression to achieve this?

My dimensions are Area, SubArea

My Measure is Max(Count)

Thanks,
Vidya

[shiveshsingh]

May be this

if(rank(sum(Count1))=1,sum(Count1))

[agomes1971]

INPUT:

LOAD Area,

     [Sub Area],

     Date,

     Count

FROM

(ooxml, embedded labels, table is Sheet2);

OUTPUT:

LOAD Area, [Sub Area], Max(Count)

Resident INPUT

group by Area, [Sub Area];

DROP Table INPUT;

[Anil_Babu_Samineni]

Not sure, You already got the answer? Aren't you?

[poluvidyasagar]

I would like to write this as one single expression. I do not want to write a load script.

[sasiparupudi1]

May be try FirstSortedValue(Count, -Date)

[jyothish8807]

You can try like this:

Dim: Area Sub Area

Exp: aggr(max(Count),Area,SubArea)

Br,

KC