# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Contributor II

## conditional cumulative sum

Hi Guys,

i have a situation where i have to calculate CUMULATIVE SUM from below table(data is an example not real)

 Region Program Program name Program Type Project name Currency Month type value PIBPOBCI Category Stage Digic # Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR Jan Forecast CAPEX 6923.19 CI IT DIGIC PRJ #11 Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR feb Forecast GFC 22.59 CI IT DIGIC PRJ #5 Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR mar Actual GFC 5345 CI IT DIGIC PRJ #7 Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR mar Actual OPEX 43 CI IT DIGIC PRJ #10 Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR april Forecast OPEX 22.59 CI IT DIGIC PRJ #8 Europe 2003/IEUR 2379 BIS IT INFRASTRUCTURE OTHERS RSWI-SP1 RSWI LESTREM EUR may Actual CAP OPEX 345 CI IT DIGIC PRJ #18

so i calculated it from below expression:-

rangesum( above( sum({\$<[type]={'Actual CAPEX'}>} value),0,rowno()))

for forecast:-

rangesum( above( sum({\$<[type]={'Forecast CAPEX'}>} value),0,rowno()))

results are fine but to show these cumulative sum in an line chart their is a problem because,

if suppose we are in Nov. so forecast before Nov. will become or equal to Actual/Initial CAPEX

like this (results are REAL DATA):-

1 Solution

Accepted Solutions
MVP

## Re: conditional cumulative sum

Try this

RangeSum(Above(If(Month(Months) <= Month(Today()), (Sum({\$<[type]={'Actual GFC'}>} value)), (sum({\$<[type]={'Forecast GFC'}>} value))), 0, RowNo()))

RangeSum(Above(If(Month(Months) <= Month(Today()), (Sum({\$<[type]={'Actual CAPEX'}>} value)), (Sum({\$<[type]={'Forecast CAPEX'}>} value))), 0, RowNo()))

So, instead of rangesumming the individual expression, rangesum the whole if statement

9 Replies
Contributor II

## Re: conditional cumulative sum

is this correct?

if(

month(Months)>=Month(today())

,(rangesum( above( sum({\$<[type]={'Actual CAPEX'}>} value),0,rowno()))) ,

(rangesum(above(sum({\$<[type]={'Forecast CAPEX'}>} value),0,rowno()))))

MVP

## Re: conditional cumulative sum

Looks good... is it not working for you?

Contributor II

## Re: conditional cumulative sum

Hi Sunny,

i got a big confusion,

if(

month(Months)<=Month(today())

,(rangesum( above( sum({\$<[type]={'Actual CAPEX'}>} value),0,rowno()))) ,

(rangesum(above(sum({\$<[type]={'Forecast CAPEX'}>} value),0,rowno()))))

this condition is working properly(BOLD) but there is a problem is:-

for example we are in March so, values of Actual should become Forecast

i mean cumulative of ACTUAL should replace cumulative of FORECAST (TILL DATE).

eg.

please see CUMULATIVE and qvf to observe my need.

Thanks & Regards

Agrim

MVP

## Re: conditional cumulative sum

You are saying we are in March? Are we really in March? I am not sure I understand how can we be in March when it is only January? I am not sure I follow what is incorrect here.

Contributor II

## Re: conditional cumulative sum

Hi Sunny,

m sorry if u understood me wrong, i was trying to explain you with example of march, bec. if i take current month(jan) as example then according to my condition i have to show cumulative sum of "FORECAST CAPEX "  but if this month would be March then, i have to show Cumulative sum of "ACTUAL CAPEX" for Jan and Feb.

logic behind this is- till month FORECAST should become ACTUAL because all the past months are gone and we got ACTUAL CAPEX instead of FORECAST CAPEX.

But if we talk about months after March (apr. may... Dec)then i have to show FORECAST CAPEX as i cannot have ACTUAL CAPEX (as its future)

so problem is i get the values from this-

if(

month(Months)<=Month(today())

,(rangesum( above( sum({\$<[type]={'Actual CAPEX'}>} value),0,rowno()))) ,

(rangesum(above(sum({\$<[type]={'Forecast CAPEX'}>} value),0,rowno()))))

(Desired Result)
but according to my example(March), calculation for cumulative FORECAST CAPEX should be like,

Jan(ACTUAL CAPEX)

Jan(ACTUAL CAPEX)+Feb(ACTUAL CAPEX)

Jan(ACTUAL CAPEX)+Feb(ACTUAL CAPEX)+Mar(FORECAST CAPEX)

Jan(ACTUAL CAPEX)+Feb(ACTUAL CAPEX)+Mar(FORECAST CAPEX)+Apr(FORECAST CAPEX)

Jan(ACTUAL CAPEX)+Feb(ACTUAL CAPEX)+Mar(FORECAST CAPEX)+Apr(FORECAST CAPEX)+May(FORECAST CAPEX).... so on till dec.

but according to my expression i am getting-

Jan(ACTUAL CAPEX)

Jan(ACTUAL CAPEX)+Feb(ACTUAL CAPEX)

Jan(FORECAST CAPEX)+Feb(FORECAST CAPEX)+Mar(FORECAST CAPEX)-----incorrect

Jan(FORECAST CAPEX)+Feb(FORECAST CAPEX)+Mar(FORECAST CAPEX)+Apr(FORECAST CAPEX)-----incorrect

Jan(FORECAST CAPEX)+Feb(FORECAST CAPEX)+Mar(FORECAST CAPEX)+Apr(FORECAST CAPEX)+Apr(FORECAST CAPEX).... so on till dec.-----incorrect

MVP

## Re: conditional cumulative sum

Can you just provide me the numerical output you are looking to get in a Excel file? I understand the logic, but I am not sure what isn't working.

Contributor II

## Re: conditional cumulative sum

Hi Sunny,

Thanks for considering my question.

My Expression-

if(

month(Months)<=Month(today())

,(rangesum( above( sum({\$<[type]={'Actual CAPEX'}>} value),0,rowno()))) ,

(rangesum(above(sum({\$<[type]={'Forecast CAPEX'}>} value),0,rowno()))))

please see excel file and desired output

Thanks & Regards

Agrim

MVP

## Re: conditional cumulative sum

Try this

RangeSum(Above(If(Month(Months) <= Month(Today()), (Sum({\$<[type]={'Actual GFC'}>} value)), (sum({\$<[type]={'Forecast GFC'}>} value))), 0, RowNo()))

RangeSum(Above(If(Month(Months) <= Month(Today()), (Sum({\$<[type]={'Actual CAPEX'}>} value)), (Sum({\$<[type]={'Forecast CAPEX'}>} value))), 0, RowNo()))

So, instead of rangesumming the individual expression, rangesum the whole if statement

Contributor II

## Re: conditional cumulative sum

THANKS A LOT SUNNY.

Regards,

Agrim