Calculating Weighted Average for Year-To-Date Column

Anonymous — Thu, 28 Jun 2018 11:03:42 GMT

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

Re: Calculating Weighted Average for Year-To-Date Column

Anonymous — Fri, 29 Jun 2018 19:44:54 GMT

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

Re: Calculating Weighted Average for Year-To-Date Column

Anil_Babu_Samineni — Sat, 30 Jun 2018 05:58:28 GMT

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))))

=(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)))

Re: Calculating Weighted Average for Year-To-Date Column

Anonymous — Mon, 02 Jul 2018 12:51:09 GMT

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

topic Re: Calculating Weighted Average for Year-To-Date Column in QlikView

Calculating Weighted Average for Year-To-Date Column

Re: Calculating Weighted Average for Year-To-Date Column

Re: Calculating Weighted Average for Year-To-Date Column

Re: Calculating Weighted Average for Year-To-Date Column