Qlik Sense help needed to calculate Run Rate formula

[piyush_s11]

Hi All,

I have a requirement where I need to derive a Run Rate formula.

The formula is 

ytd till Month of the field [Value_in_chf] + avg(last three months (including the selected month)) of the field [Value_in_chf] *12-Month

for Ex:  if we consider the month as May of year 2021 then the above formula will be 

ytd of [Value_in_chf] till month May + avg of [Value_in_chf] for (March+April+May) last three months * 7

here considered 7 because from May till December there are 7 months remaining.

The fields I have used as below

1) For MonthName (ex: May) the field in my script is Month

2) For Year the field in my script is year

Please Note: Consider the year as well while providing solution.

Thanks in Advance.

@avinashelite @tresesco @MayilVahanan @sunny_talwar @Anil_Babu_Samineni @kaushiknsolanki @prabir_c @its_anandrjs @kushalthakral @ChristofSchwarz 

[chrismarlow]

Hi,

Maybe the following will help. I've created a MonthYear that is a date, as working with separate year and month fields I think will be a bit painful (script at the bottom). Needs a bit of work on the formatting, but not sure how you want to display it.

Sum(Value_in_chf)+RangeAvg(above(Sum(Value_in_chf),0,3))*(12-Month(MonthYear))

Gives;

Cheers,

Chris.

Toy script;

data:
Load
	makedate(Year,Month,1) as MonthYear,
	*;
Load * Inline [
Month, Year, Value_in_chf
11, 2020, 1
12, 2020, 2
1, 2021, 3
2, 2021, 4
3, 2021, 5
4, 2021, 6
5, 2021,7
]

[piyush_s11]

Hi @chrismarlow ,

unfortunately the solution provided by you doesn't worked. 

also you missed the YTD for sum(value_in _CHF) which I have mentioned in the formula.

thanks 

[chrismarlow]

Hi,

You could replace the Sum(Value_in_chf) with a RangeSum, so try;

RangeSum(above(Sum(Value_in_chf),0,Month(MonthYear)))+RangeAvg(above(Sum(Value_in_chf),0,3))*(12-Month(MonthYear))

Cheers,

Chris.