topic Re: Conditional Sum in QlikView

Conditional Sum

Anonymous — Tue, 17 Dec 2013 13:45:48 GMT

I need to create new fields giving me YTD figures for Budget.

So say

YTD1 80111.75

YTD2 160223.5 (adding budget for period 1 & 2)

YTD3 240335.25 (adding budget for periods 1,2 & 3)

Cost Centre	Account	Period	Budget
12345	953211	1	80111.75
12345	953211	2	80111.75
12345	953211	3	80111.75
12345	953211	4	80111.75
12345	953211	5	80111.75
12345	953211	6	80111.75
12345	953211	7	80111.75
12345	953211	8	80111.75
12345	953211	9	80111.75
12345	953211	10	80111.75
12345	953211	11	80111.75
12345	953211	12	80111.75

Regards

Re: Conditional Sum

Anonymous — Tue, 17 Dec 2013 20:09:09 GMT

LOAD [Cost Center],

Account,

Period,

Budget,

Period * Budget as YTD

FROM

[Tablename];

Re: Conditional Sum

Anonymous — Wed, 18 Dec 2013 09:33:53 GMT

Thanks Vamshi

Sorry I have should have mentioned that budget value per month can vary so cant do a simple times.

Regards

Re: Conditional Sum

sujeetsingh — Wed, 18 Dec 2013 09:40:14 GMT

please explain in details what you want

Re: Conditional Sum

Anonymous — Wed, 18 Dec 2013 09:48:40 GMT

I think the RangeSum() and Peek() functions can help you here. First you need to load your data and then perform a resident load with the RangeSum() function calculating the cumulative YTD value. The Peek() function takes the value of the previous row for the selected field. Finally we drop the original data table.

BaseData:

LOAD [Cost Centre],

Account,

Period,

Budget

From [Data Source]

CalculatedData:

NoConcatenate

LOAD [Cost Centre],

Account,

Period,

Budget,

RangeSum(Budget, Peek('YTD')) as YTD

RESIDENT BaseData;

DROP TABLE BaseData;

Re: Conditional Sum

Anonymous — Wed, 18 Dec 2013 09:54:32 GMT

Hi Sujeet

I am trying to create a Budget cost centre report which has the following figures

Month YTD

Cost Centre Code Annual Budget Bugdet Spend Variance Budget YTD Spend YTD Variance YTD

I am pulling the data from an Oracle database which doesnt have a YTD field. The raw would look something like:

Period Budget Cost Centre Account

1 1000 ****** *******
2 1200

3 1100

4 1300

Spend field would data in same format.

Regards

Re: Conditional Sum

israrkhan — Wed, 18 Dec 2013 10:01:25 GMT

try

Rangsum(Above(Sum(Budget),0, rowno()))