Sum based on condition

[#1]

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.

[pokassov · Accepted solution]

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;

[Anil_Babu_Samineni]

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))

[Anonymous]

Use this expression in result

Rangesum(Below(sum(Sale),0,count(total<ID> Sale)+1-RowNo()))

[#5]

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

[#6]

Thanks Anil

[#7]

Thanks Shiva

[pokassov]

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

[#9]

Thanks heaps Sergey.. Appreciate your help.