2 Replies Latest reply: Sep 28, 2017 8:24 AM by Matthias Walter RSS

    Rangesum() in Loading Script

    Matthias Walter

      Hello everybody,

      I want to transfer a formula from a Dashboard to the loadingscript. The Data itself is stuctured as followed:

       

      DayAmountterminated (boolean)
      1100
      120

      1

      1300
      2201
      2400
      2600
      31000

       

      Right now I use the following formula in a Diagram:

      RangeSum(above(sum({$<[terminated (boolean)]=1>}Amount]),0,rowno(total)))

      and get the total aggregated amount for each Day, Day1 60, Day2 180, Day3 280 and thats perfect.

      to transfer it to the script without loosing information, my first step was to add the column "terminated Amount" with the formula:

      if(terminated (boolean)=1,Amount) as [terminated Amount]

       

      using this script now:

       

      load

      Day,

      sum(Amount),

      sum([terminated (boolean)]) as [terminated (boolean)],

      sum([terminated Amount]) as [terminated Amount]

      resident XX

      order by Day

      group by Day;

       

      brings me to the following table:

      DayAmountterminated (boolean)terminated Amount
      160120
      2120120
      310000

       

      Now I want to add the aggregated amounts like I made it with Rangesum(), somehow it doesnt work in the Script.

      The Table I want in the end looks like this:

       

      DayAmountaggrAmountterminated AmountaggrTerminatedAmount
      160602020
      21201802040
      3100280040

       

      Can someone please help me with it? I'm sure its just a tiny error in my script, but it's consuming a lot of time now...

      Thanks!