Hello everybody,
I want to transfer a formula from a Dashboard to the loadingscript. The Data itself is stuctured as followed:
| Day | Amount | terminated (boolean) |
|---|
| 1 | 10 | 0 |
| 1 | 20 | 1 |
| 1 | 30 | 0 |
| 2 | 20 | 1 |
| 2 | 40 | 0 |
| 2 | 60 | 0 |
| 3 | 100 | 0 |
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:
| Day | Amount | terminated (boolean) | terminated Amount |
|---|
| 1 | 60 | 1 | 20 |
| 2 | 120 | 1 | 20 |
| 3 | 100 | 0 | 0 |
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:
| Day | Amount | aggrAmount | terminated Amount | aggrTerminatedAmount |
|---|
| 1 | 60 | 60 | 20 | 20 |
| 2 | 120 | 180 | 20 | 40 |
| 3 | 100 | 280 | 0 | 40 |
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!
