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
