Find highest Month and Value

[beunderf]

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

Thank you for your help!!

Frank

[marcus_malinow]

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

LOAD * INLINE [
    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:
LOAD *,
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

[CELAMBARASAN · Accepted solution]

Try with firstsortedvalue

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

[#4]

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:

LOAD

    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)

LOAD

    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

[#5]

Hi

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

Thanks & Regards,

Amuthabharathi.

[beunderf]

That was what I needed! Thank you very much! 🙂