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

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 ! :-)

 

Attachments

AttachmentSize
Image icon example.jpg80.88 KB

3 Comments

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 - Oct 19, 2020 - 11:57pm

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 

 

tyger0951 - Oct 19, 2020 - 12:20pm

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.

fabd - Oct 19, 2020 - 6:10am
View More Comments + Add a Comment

(4) Answers

Login