Need help with cumulative sum in the back end

Dave1 · ‎2023-04-05

Hi Everyone ,

I am trying to get a cumulative sum of a table with multiple columns . I was able to achieve this in front end easily using this expression RANGESUM (above(total((([ Sales Units] +[Inventory Units])-([Avg Qty]))),0,RowNo(TOTAL)) . But when i am trying to replicate the same thing in script i am not able to do it . Any help would be greatly Appreciated.

Sample Data :

tab1:
LOAD * INLINE [
Week, SalesUnits, Inv units,Avgqty,
    202301, 100, 500, 300
    202302, 500, 0,300
    202303, 600, 0,300
    202304, 700, 0,300
    202305, 900, 0,300

];

Expected output

week, Cumulative sum

202301, 300

2020302, 500

2020303,800,

2020304,1200

2020305, 1800

MarcoWedel · ‎2023-04-05

tab1:
LOAD *,
     RangeSum(SalesUnits+[Inv units]-Avgqty,Peek(CumulativeSum)) as CumulativeSum
INLINE [
    Week, SalesUnits, Inv units, Avgqty
    202301, 100, 500, 300
    202302, 500, 0, 300
    202303, 600, 0, 300
    202304, 700, 0, 300
    202305, 900, 0, 300
];

Dave1 · ‎2023-04-05

It is calculating but getting duplicates for week

General Question