RangeSum, issues with negatives

[Lena4]

Hi, I'm wanting to sum a table range based on ID number, Year and Month.  The formula works well if all numbers are positive. But it breaks when there are negative numbers. 

Here's my code - Any suggestions for handling the negatives?

Rangesum(
        Above(total sum({<IDNUMBER> } AMOUNT) ,0,
             aggr(rowno(),IDNUMBER (S_Year, (numeric, ASCENDING)),(S_Month, (numeric, ASCENDING))
      ))

)

[Anil_Babu_Samineni]

Not sure the idea to sort the order at column, but you can try this?

Rangesum( Above(total sum({<IDNUMBER> } AMOUNT) ,0,Rowno(TOTAL)))

[diegozecchini]

Hi

the answer from @Anil_Babu_Samineni is perfect, try it firstly.
If the issue persists, you might want to force a sorted aggregation like this:

RangeSum(
Above(
Total Sum({<IDNUMBER>} AMOUNT),
0,
Aggr(
RowNo(), IDNUMBER, (S_Year, ASCENDING), (S_Month, ASCENDING)
)
)
)

[Lena4]

@Anil_Babu_Samineni @diegozecchini     

Thank you, I tried both solutions but didn't quite get the expected result. The first one provided a running total for everything in the chart and the second gave running totals for each IDnumber but, they weren't quite right. 

. Any ideas about how to fix this?

I think the problem is in the aggregation at the end. Here's a sample of the result from that function. It's messing up the numbering on the last row of each idnumber. 

[Aditya_Chitale]

@Lena4 ,

Maybe it's because your month field is in string format. Try using month() function to convert it. Hope this will resolve your issue. If this still doesn't work, please share sample data.

Regards,

Aditya

[diegozecchini]

Hi
Try this formula instead:

RangeSum(
   Above(
       Total Sum({<IDNUMBER>} AMOUNT),
        0,
        Aggr(
             RowNo(TOTAL),
             IDNUMBER,
             (S_Year, ASCENDING),
             (S_Month, ASCENDING)
         )
    )
)

Using RowNo(TOTAL) within Aggr() should avoid inconsistencies at the last row of each IDNUMBER.

[Lena4]

@diegozecchini Adding Total sums all of the rows together, no matter the ID number.

@Aditya_Chitale I tried but adding that function at the end of the aggregation causes an error.

  I have an idea for handling this in the script that I'm going to try, thanks for working through it with me. 

[diegozecchini]

Thank you for your update @Lena4, I am curious if you find the solution 🙂