topic Re: need to get the cummulative sum in QlikView

need to get the cummulative sum

suvechha_b — Thu, 09 Jul 2015 12:05:50 GMT

Hi ,

How to build the following chart :

Input
Trading Day	1	2	3	4	5
Sales	500	1000	2000	500	1000
Budget	0	0	20000	0	0
Budget=20000/5 is 4000
Output
Trading Day	1	2	3	4	5
Sales	500	1500	3500	4000	5000
Budget	4000	8000	12000	16000	20000

Please find the exel file attached

Re: need to get the cummulative sum

suvechha_b — Thu, 09 Jul 2015 12:10:14 GMT

Note : My Sales is calculated by variable : (vPerSales)

and budget : (vPerBudget) which is calculated monthly basis . eg, 20000

Re: need to get the cummulative sum

Anonymous — Thu, 09 Jul 2015 12:18:25 GMT

tab expression

define "full accumulation"

does it work as you expected?

Re: need to get the cummulative sum

sunny_talwar — Thu, 09 Jul 2015 12:28:49 GMT

Script:

Table:

LOAD [Trading Day],

Sales,

Budget

FROM

Community_171870.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD Sum(Budget)/Count(Budget) as Budget1

Resident Table;

Output:

Method 1 is Full accumulation method

Method 2 is RangeSum(Above()) Method:

=RangeSum(Above(Sum(Budget1), 0, RowNo()))

=RangeSum(Above(Sum(Sales), 0, RowNo()))

HTH

Best,

Sunny

Re: need to get the cummulative sum

suvechha_b — Thu, 09 Jul 2015 13:09:01 GMT

Hello Sunny ,

My Sales and Budget are calculated by variable

eg,

vBudget = sum({$<TranType = {'Budget_$(vAmountType)'},CurrentPer = {1},Fiscal_Year = {$(=max(Fiscal_Year))}>} Amount)

So , can you please give me the expression :

LOAD Sum(Budget)/Count(Budget) as Budget1

as , Budget is a variable it is not working

and

Method 2 is RangeSum(Above()) Method:

=RangeSum(Above(Sum(Budget1), 0, RowNo()))

=RangeSum(Above(Sum(Sales), 0, RowNo()))

Re: need to get the cummulative sum

suvechha_b — Thu, 09 Jul 2015 18:50:44 GMT

Input
Trading Day	1	2	3	4	5
Sales	500	1000	2000	500	1000
Budget	0	0	20000	0	0

where Sales is calculated with the help of variable , eg , vSales

vSales = sum({$<TranType = {'Sales_$(vAmountType)'},CurrentPer = {1},Fiscal_Year = {$(=max(Fiscal_Year))}>} Amount)

vBudget = sum({$<TranType = {'Budget_$(vAmountType)'},CurrentPer = {1},Fiscal_Year = {$(=max(Fiscal_Year))}>} Amount)

Can anyone tell me how to write the following expression :

LOAD Sum(Budget)/Count(Budget) as Budget1

as vBudget / count(vBudget ) is also not working

and

Method 2 is RangeSum(Above()) Method:

=RangeSum(Above(vSales ), 0, RowNo()))

=RangeSum(Above(vBudget), 0, RowNo())) are not working

Cummulative Expression