Dynamically sum latest values per case

[beunderf]

Dear all,

I have the following question. How do I create an expression that dynamically sums the latest values per case, depending on my selection. I have the following example:

Rownr	Casenumber	Month	MonthStep	Value	Monthnr	KPI
1	1	Jan	1	5	1	KPI
2	1	Jan	2	6	1	KPI
3	2	Jan	1	4	1	KPI
4	1	Feb	1	7	2	KPI
5	3	Mar	1	5	3	KPI
6	3	Mar	2	4	3	KPI
7	1	Mar	1	5	3	KPI

As you can see, within a month a case can have more values. The highest 'month step' is the last value entered in a month.

Now I want to SUM(Values), but only the last Value per case, depending on my selection.

So if I select 'Jan', I want to sum 6 for Case_1 and 4 for Case_2, totals 10

If I select 'Jan, Feb', then the latest value for Case_1=7 and Case_2 =4, total 11

If I select 'Jan, Feb, Mar', then the latest value for Case_1 =5, Case_2= 4 and Case_3=4, total 13

How do I achieve this? I attached my example to question.

Thanks!

[#2]

Look at the attacchment.

Hope this helps.

MC

[beunderf]

Hi Mario,

This is not what I am looking for. with MAX I get the Highest value per case, but this is not what I want. If you look at Case_1, it has a lower Value in March (5), but the Value in March is the 'latest' value. When I select 'Jan, Feb, Mar' I want to see the latest values entered, not the highest values, if you understand what I mean.

[maxgro · Accepted solution]

try with

dim     Casenumber

exp     FirstSortedValue(Value, -Rownr)

[#5]

Attached with the answer you are looking for.

Hope it helps

Thanks

AJ