Sum value with conditions in other columns give by the treated row for over rows

[Sylvain Clement]

Hello,

I want to sum value where conditions are gives in the row (yellow in picture) to select over rows which have to be sum. The two columns "RankMove PM n-1" and "RankMove PM" are conditions for over rows. The sum have to sum only rows where EQTNAME is the same.

If NAME_PM is null, sum = 0

else

sum "trackout" if "RankMove" > "RankMove PM" and "Rankmove" < "RankMove PM n-1"

Someone has an idea Is it possible in spotfire to make this

It's like a SumIf in excel.

Thanks a lot for your help ! :-)

[Fabian Duerr]

Of course you can do SumIfs in Spotfire.

 

Can you please share your data with expected outcome, for example as Excel file. Then we can wotk on this.

[Sylvain Clement]

Hello,

Excel file in attached.

I put the Excel Formula in the output column.

Thanks for your help,

[Tyger Guzman 2]

The format for a sumIF statement looks like : 

 

Sum(IF [Column] = x , [Column], Null))

 

So for your exact use should look something like : 

 

Case when NAME_PM is null then 0  
else  
Sum(IF([RankMove] > [RankeMove PM] and [RankMove] < [RankMove PM n-1],[trackout],0   ))
end 

 

 

[Sylvain Clement]

It doesn't work, I think that the conditions for rankmove are execute only on the selected row. In a "Sumif" excel, the sum is on all rows and conditions of Sumif are in the selected row.

 

I try to make this with Python script because I am not sure that it's possible with Calculated Column.

 

I don't know python so it's taking time !!

[Sylvain Clement]

Nobody has a solution with calculated column

[Tyger Guzman 2]

The closest I got was using an expression to bucket the rows :

SN(Last([RankMove PM n-1]) over (allPrevious([RankMove])),0)Then using the following expression to sum each group

Sum([Trackout]) OVER (intersect([bucketRank]))I was having trouble getting it completely accurate as your some row items would fall under multiple buckets. But hopefully this helps you get started.

[Fabian Duerr]

Attached you can find a solution where I didn't use your RankMove columns.

I used the cummulative sum and then transformed your data table. Then using a OVER Intersect() function to get the value from the next PM. It's important to know that Intersect functions need a continous scale, so I had to calculate a rank for each PM first.

All this can probably also be done in your original data table without my transformation. But I already spent too much time on this. You'll get this done! ;)

[Sylvain Clement]

Excellent, thank you very much for your time and your help !!

 

I have to adapt with many EQTNAME  but I think it won't be a difficulty!

[Sylvain Clement]

Thanks for your help and your time !