
Re: conditional cumulative sum
Agrim Sharma Jan 29, 2018 11:57 PM (in response to Agrim Sharma)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()))))

Re: conditional cumulative sum
Sunny Talwar Jan 30, 2018 8:41 AM (in response to Agrim Sharma)Looks good... is it not working for you?

Re: conditional cumulative sum
Agrim Sharma Jan 31, 2018 2:03 AM (in response to Sunny Talwar )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

Automated Trend  Copy.qvf 288.0 K

CUMULATIVE.xlsx 12.8 K

Re: conditional cumulative sum
Sunny Talwar Jan 31, 2018 7:19 AM (in response to Agrim Sharma)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.

Re: conditional cumulative sum
Agrim Sharma Jan 31, 2018 11:44 PM (in response to Sunny Talwar )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

Re: conditional cumulative sum
Sunny Talwar Feb 1, 2018 7:39 AM (in response to Agrim Sharma)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.

Re: conditional cumulative sum
Agrim Sharma Feb 2, 2018 1:43 AM (in response to Sunny Talwar )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

CUMULATIVE ERROR.xlsx 14.9 K

Re: conditional cumulative sum
Sunny Talwar Feb 2, 2018 7:30 AM (in response to Agrim Sharma)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

Automated Trend  Copy (1).qvf 288.0 K

Re: conditional cumulative sum
Agrim Sharma Feb 6, 2018 1:52 AM (in response to Sunny Talwar )THANKS A LOT SUNNY.
Regards,
Agrim







