topic Re: Sum b in QlikView

Sum based on condition

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sum b

pokassov — Tue, 04 Apr 2017 09:39:49 GMT

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;

Re: Sum b

Anil_Babu_Samineni — Tue, 04 Apr 2017 09:41:03 GMT

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

Re: Sum b

Anonymous — Tue, 04 Apr 2017 09:45:25 GMT

Use this expression in result

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

Re: Sum b

Wed, 05 Apr 2017 11:51:49 GMT

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

Re: Sum b

Wed, 05 Apr 2017 11:52:25 GMT

Thanks Anil

Re: Sum b

Wed, 05 Apr 2017 11:52:34 GMT

Thanks Shiva

Re: Sum based on condition

pokassov — Wed, 05 Apr 2017 12:26:16 GMT

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

Re: Sum based on condition

Thu, 06 Apr 2017 02:28:01 GMT

Thanks heaps Sergey.. Appreciate your help.