Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
piyush_s11
Creator
Creator

Qlik Sense help neededd to calculate Run Rate formula

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 

 

3 Replies
chrismarlow
Specialist II
Specialist II

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;

20210603_1.png

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
Creator
Creator
Author

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
Specialist II
Specialist II

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.