Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Need your help as I tried everything and gave up..
My problem is to sum of forecast sales numbers for rest of the months for each project ID.
Following is the data I have
ID | Date | Sale |
---|---|---|
101 | 1/March/2017 | 10 |
101 | 2/March/2017 | 20 |
101 | 3/March/2017 | 10 |
101 | 4/March/2017 | 10 |
102 | 5/March/2017 | 20 |
102 | 6/March/2017 | 10 |
102 | 7/March/2017 | 20 |
Answer-set should look like: Summing all Dates Sales data for individual ID and for next date ignoring the previous date and summing what rest is left
ID | Date | Result |
---|---|---|
101 | 1/March/2017 | 50 |
101 | 2/March/2017 | 40 |
101 | 3/March/2017 | 20 |
101 | 4/March/2017 | 10 |
102 | 5/March/2017 | 50 |
102 | 6/March/2017 | 30 |
102 | 7/March/2017 | 20 |
I hope to get some guidance I tried everything and not reaching anywhere.
Hello
In script:
[sum forecasts]:
load
ID,
Date,
RangeSum(if(previous(ID)=ID, peek(Result),0),Sale) As Result
Resident forecasts
Order by ID,
Date desc;
Hello
In script:
[sum forecasts]:
load
ID,
Date,
RangeSum(if(previous(ID)=ID, peek(Result),0),Sale) As Result
Resident forecasts
Order by ID,
Date desc;
May be use this for result
If(Above(Aggr(Sum(Sale),ID),0),Above(Aggr(Sum(Sale),ID),0),Above(Result - Sum(Sale),1,3))
Use this expression in result
Rangesum(Below(sum(Sale),0,count(total<ID> Sale)+1-RowNo()))
Hi Sergey
Thanks heaps. It worked. Appreciate if you please help in understanding what is happening in this function so I can use it in future.
Regards
Thanks Anil
Thanks Shiva
Hi John
When we use a resident load we can use clause "Order By"
So all of your data will be correctly (as we need) ordered
Function previous uses value of a field from previous row of the resulting table.
Function peek is almost the same, but you can use value from a new field that can be generated by the load.
RangeSum simply sums two argument.
But it's convinient for situations when one of the arguments is null - the result won't be null.
Best regards, Sergey
Thanks heaps Sergey.. Appreciate your help.