Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Look at the attacchment.
Hope this helps.
MC
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.
try with
dim Casenumber
exp FirstSortedValue(Value, -Rownr)
Attached with the answer you are looking for.
Hope it helps
Thanks
AJ