5 Replies Latest reply: Aug 28, 2013 12:06 PM by Frank Beunder

# Find highest Month and Value

Dear All,

I have the following example data set:

 Year Case Month Value 2013 390191 1 2 2013 390191 2 2 2013 390191 3 3 2013 390191 4 3 2013 390191 5 4 2013 390191 6 4 2013 390191 7 5 2013 390191 8 6 2013 434002 1 2 2013 434002 2 4 2013 434002 3 1

I want to create a table per year, that sums the values of the highest available month per Case.

In this example for case 390191 the highest month is 8 with value 6 and case 434002 has highest month 3 with value 1.

So the result should be:

 Year Sum(value) 2013 7

How do I achief this??

Frank

• ###### Re: Find highest Month and Value

Frank,

just taken a look at this and

1 - I've changed your load script to concatenate month and case into a new field MonthCase

Year, Case, Month, Value
2013, 390191, 1, 2
2013, 390191, 2, 2
2013, 390191, 3, 3
2013, 390191, 4, 3
2013, 390191, 5, 4
2013, 390191, 6, 4
2013, 390191, 7, 5
2013, 390191, 8, 6
2013, 434002, 1, 2
2013, 434002, 2, 4
2013, 434002, 3, 1
]
;

Data:
Num(Text(Month) & Text(Case)) as MonthCase
RESIDENT TmpData;

DROP TABLE TmpData;

Then, I can display a table showing your Year dimension and this expression:

Sum({\$<MonthCase={\$(=Concat(Aggr(Max(MonthCase),Year,Case),','))}>} Value)

Hope this helps,

Marcus

• ###### Re: Find highest Month and Value

Try with firstsortedvalue

=Sum(Aggr(FirstSortedValue(Value, -Month) ,Case))

• ###### Re: Find highest Month and Value

That was what I needed! Thank you very much! :-)

• ###### Re: Find highest Month and Value

Hi Frank,

To do what you describe, I think you should do it in 3 steps:

1 - In the script, load in a temporary table the year, the case and the max month like this :

TEMP:

Year,

Case,

Max(Month) as Month

RESIDENT

DATA

GROUP BY

Year, Case;

2 - Always in the script, join the datas from the temporary table and a flag with your data table and drop the temporayr table like this :

Left Join (DATA)

Year,

Case,

Month,

1 as [Flag max month]

RESIDENT

TEMP;

DROP TABLE TEMP;

3 - The to sum the values of the highest available month per Case, use this expression :

=Sum({\$<[Flag max month] = {1}>} Value)

I attach an application with this example.

Hope it helps.

Regards,

Nicolas

• ###### Re: Find highest Month and Value

Hi

please find the attached file . hope will it helps you.

Thanks & Regards,

Amuthabharathi.