sum only the lastest value reported

[Jean Philippe GERNEZ]

Hello,

Consider the Spotfire table below. For each machine I have anestimated valuereported over a period of time.

I want to sum, for each date, the last value reported per machine.

For example

 

 

 

 

machine

extimation date

value

expected result

reason

 

 

 

A

 

01/01/2020

0.2

0.2

sum(last A=0.2)

 

 

B

02/01/2020

0.3

0.5

sum(last A=0.2, last B=0.3)

 

 

A

05/01/2020

0.1

 

0.4

 

sum(last A=0.1, last B=0.3)

 

 

C

08/01/2020

0.2

0.6

 

 

 

 

sum(last A=0.2, last B=0.3, last C=0.6)

 

 

 

 

 

 

 

B

15/01/2020

0.5

1.3

 

 

 

 

sum (last A=0.2, last B=0.5, last C=0.6)

 

 

 

 

 

 

 

 

I try to add a rowid with this formula:

RowId= DenseRank([estimation date])

And after calculate the result like this:

result= Sum(Last([value]) OVER (Intersect([machine],AllPrevious([RowId])))) OVER (AllPrevious([RowId]))

I also try this:

result= sum(LastValueForMax([RowId],[value]) OVER (Intersect([machine],AllPrevious([RowId])))) OVER (AllPrevious([RID]))

but the result is always wrong, in each case I sum all the previous values not only the more recent per machine.

has someone ever had the same issue

[Khushboo Rabadia]

Can you provide the sample dxp. In the dataset provided above, sum (last A=0.2, last B=0.5, last C=0.6), last A would be either 0.4 or 0.1, how come 0.2 It is bit confusing.