Calculating Weighted Average for Year-To-Date Column

[Anonymous]

Hi All,

Can you assist in simplifying the below expression which currently consumes memory on our server, to a point where the screen becomes blank.

The idea is to create a weighted average year-to-date value, in the balance sheet.  The example is below:

Product	Year Month	Value	Number of Days in the month
Card	201801	1500000	31
Card	201802	2000000	28
Card	201803	5057667	31
Card	201804	2345656	30
Card	201805	3000006	31
Card	201806	4809999	30
VAF	201803	2000000	31
			212

The Result should be an average YTD-201806. This after calculating as follows:

201801 = Value * Num_Of_Days_In Month

201802 = Value * Num_Of_Days_In Month <-dynamic number as per calendar

201803 = Value * Num_Of_Days_In Month

201804 = Value * Num_Of_Days_In Month

201805 = Value * Num_Of_Days_In Month

201806 = Value * Num_Of_Days_In Month

/

Total Number of Days(212) This number is dynamic based on which month you have selected

Product	May'18 - YTD
Card	3689990

The below is the current script which works, but very resource intensive. Hopefully some can help. Thanks

=((Sum( {<Year_Month={$(v1stMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31) + (Sum( {<Year_Month={$(v1stMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v2ndMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*vNumofDaysFeb_PYTD) + (Sum( {<Year_Month={$(v2ndMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*vNumofDaysFeb_PYTD))

+

((Sum( {<Year_Month={$(v3rdMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31) + (Sum( {<Year_Month={$(v3rdMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v4thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*30) + (Sum( {<Year_Month={$(v4thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*30))

+

((Sum( {<Year_Month={$(v5thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31) + (Sum( {<Year_Month={$(v5thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v6thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*30) + (Sum( {<Year_Month={$(v6thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*30))

+

((Sum( {<Year_Month={$(v7thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31) + (Sum( {<Year_Month={$(v7thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v8thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31) + (Sum( {<Year_Month={$(v8thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v9thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*30) + (Sum( {<Year_Month={$(v9thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*30))

+

((Sum( {<Year_Month={$(v10thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31)+ (Sum( {<Year_Month={$(v10thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

+

((Sum( {<Year_Month={$(v11thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*30)+ (Sum( {<Year_Month={$(v11thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*30))

+

((Sum( {<Year_Month={$(v12thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance)*31)  + (Sum( {<Year_Month={$(v12thMonth_PrevYear)},Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance)*31))

)/vNumofdays_PYTD

[Anonymous]

Is anyone able to assist on this? Qlik experts???

[Anil_Babu_Samineni]

Does this improves the performance?

=RangeSum((Sum( {<Year_Month={$(v1stMonth_PrevYear),$(v2ndMonth_PrevYear),$(v3rdMonth_PrevYear),$(v4thMonth_PrevYear),$(v5thMonth_PrevYear),$(v6thMonth_PrevYear),

$(v7thMonth_PrevYear),$(v8thMonth_PrevYear),$(v9thMonth_PrevYear),$(v10thMonth_PrevYear),$(v11thMonth_PrevYear),$(v12thMonth_PrevYear)},

Financial_Product_Category={'BCA / OD','CA'}>}Performing_Average_Credit_Balance) * (vNumofDaysFeb_PYTD+(31*7)+(30*4))),

(Sum( {<Year_Month={$(v1stMonth_PrevYear),$(v2ndMonth_PrevYear),$(v3rdMonth_PrevYear),$(v4thMonth_PrevYear),$(v5thMonth_PrevYear),$(v6thMonth_PrevYear),

$(v7thMonth_PrevYear),$(v8thMonth_PrevYear),$(v9thMonth_PrevYear),$(v10thMonth_PrevYear),$(v11thMonth_PrevYear),$(v12thMonth_PrevYear)},

Financial_Product_Category={'BCA / OD','CA'}>}Locked_Average_Credit_Balance) * (vNumofDaysFeb_PYTD+(31*7)+(30*4))))

Or

=(Sum( {<Year_Month={$(v1stMonth_PrevYear),$(v2ndMonth_PrevYear),$(v3rdMonth_PrevYear),$(v4thMonth_PrevYear),$(v5thMonth_PrevYear),$(v6thMonth_PrevYear),

$(v7thMonth_PrevYear),$(v8thMonth_PrevYear),$(v9thMonth_PrevYear),$(v10thMonth_PrevYear),$(v11thMonth_PrevYear),$(v12thMonth_PrevYear)},

Financial_Product_Category={'BCA / OD','CA'}>} (Performing_Average_Credit_Balance+Locked_Average_Credit_Balance)) * (vNumofDaysFeb_PYTD+(31*7)+(30*4)))

[Anonymous]

Thanks Anil, let me give it a try and I will give feedback.