Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

naheemali
New Contributor

Conditional Sum

Hi

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 CentreAccountPeriodBudget
12345953211180111.75
12345953211280111.75
12345953211380111.75
12345953211480111.75
12345953211580111.75
12345953211680111.75
12345953211780111.75
12345953211880111.75
12345953211980111.75
123459532111080111.75
123459532111180111.75
123459532111280111.75

Regards

6 Replies
vamshi_1957
Contributor II

Re: Conditional Sum

LOAD [Cost Center],

     Account,

     Period,

     Budget,

     Period * Budget as YTD

FROM

[Tablename];

naheemali
New Contributor

Re: Conditional Sum

Thanks Vamshi

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

Regards


sujeetsingh
Honored Contributor III

Re: Conditional Sum

please explain in details what you want

Highlighted
abh
Contributor II

Re: Conditional Sum

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;

naheemali
New Contributor

Re: Conditional Sum

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

israrkhan
Valued Contributor II

Re: Conditional Sum

try

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

Community Browser