topic Re: Dynamically sum latest values per case in QlikView

Dynamically sum latest values per case

beunderf — Thu, 31 Jul 2014 15:45:56 GMT

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!

Re: Dynamically sum latest values per case

Thu, 31 Jul 2014 15:51:34 GMT

Look at the attacchment.

Hope this helps.

Re: Dynamically sum latest values per case

beunderf — Thu, 31 Jul 2014 16:08:35 GMT

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.

Re: Dynamically sum latest values per case

maxgro — Thu, 31 Jul 2014 20:09:23 GMT

try with

dim Casenumber

exp FirstSortedValue(Value, -Rownr)

Re: Dynamically sum latest values per case

Thu, 31 Jul 2014 20:53:53 GMT

Attached with the answer you are looking for.

Hope it helps

Thanks