Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

RegionProgramProgram nameProgram TypeProject nameCurrencyMonthtypevaluePIBPOBCICategoryStageDigic #
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURJanForecast CAPEX6923.19CI ITDIGICPRJ#11
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURfebForecast GFC22.59CI ITDIGICPRJ#5
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURmarActual GFC5345CI ITDIGICPRJ#7
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURmarActual OPEX43CI ITDIGICPRJ#10
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURaprilForecast OPEX22.59CI ITDIGICPRJ#8
Europe2003/IEUR2379 BIS IT INFRASTRUCTUREOTHERSRSWI-SP1 RSWI LESTREMEURmayActual CAP OPEX345CI ITDIGICPRJ#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):-

    

x.PNG

so what will be my expression for getting Forecast CAPEX till Oct., please help.

1 Solution

Accepted Solutions
MVP
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

Capture.PNG

9 Replies
agrimroquette
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
MVP

Re: conditional cumulative sum

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

agrimroquette
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
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.

agrimroquette
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
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.

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

Capture.PNG

agrimroquette
Contributor II

Re: conditional cumulative sum

THANKS A LOT SUNNY.

Regards,

Agrim

Community Browser