YTD Set Analysis for Weighted Average

[mjperreault]

Hi All,

I am struggling to put together a formula and was wondering if anyone has any ideas.

I have data such as the below (I've also attached the excel file with formulas for reference).  The columns I actually have in my data are [Month Year],[Year],[Month],[Days_In_Month],[Inflows_Outflows],[Rolling_Total].  I want to be able to dynamically calculate Weighted_Total in Qlik.

Month Year	Year	Month	Days_In_Month	Inflows_Outflows	Rolling_Total	Weighted_Total
Jan-19	2019	Jan	31	5,000,000.00	186,123,300.00	186,123,300.00
Feb-19	2019	Feb	28	-	186,123,300.00	186,123,300.00
Mar-19	2019	Mar	31	(489,699.00)	185,633,601.00	185,954,625.90
Apr-19	2019	Apr	30	-	185,633,601.00	185,874,369.68
May-19	2019	May	31	-	185,633,601.00	185,824,940.34
Jun-19	2019	Jun	30	250,000.00	185,883,601.00	185,834,663.10
Jul-19	2019	Jul	31	-	185,883,601.00	185,841,819.12
Aug-19	2019	Aug	31	-	185,883,601.00	185,847,149.32
Sep-19	2019	Sep	30	-	185,883,601.00	185,851,155.00
Oct-19	2019	Oct	31	(100,000.00)	185,783,601.00	185,844,266.27
Nov-19	2019	Nov	30	-	185,783,601.00	185,838,817.29
Dec-19	2019	Dec	31	-	185,783,601.00	185,834,127.69

The Weighted_Total column is calculated using a Weighted Average of the Rolling_Total and Days in Month which is then rolled along through the remainder of the months.

My goal is to be able to select one Month Year in Qlik and have a formula for a KPI that calculated the Weighted_Total.

@sunny_talwar  was wondering if you had any ideas

Thanks!
Mark

[vunguyenq89 · Accepted solution]

Hi,

You can use Set Analysis for the measure. For example, try the KPI measure below:

=If(GetSelectedCount([Month Year]) = 1,
Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Rolling_Total * Days_In_Month) / Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Days_In_Month)
,'Please select one MonthYear')

Hope this helps,

BR,

Vu Nguyen