9 Replies Latest reply: Jan 5, 2018 1:22 PM by Rob Wunderlich RSS

    Incorrect totals when I use IF statement in the expression

    Fayez Khan

      Hi I am using an expression

       

      IF(PERIOD<>PERIOD_NEWLOANS,

      (
      SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}Loan_Spread)/
      SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}AVERAGE_BALANCE)
      )* 
      $(=$(v_No_of_YearDays))/Ceil(MonthEnd(Date#(max(PERIOD),'YYYYMM'))-MonthStart(Date#(max(PERIOD),'YYYYMM')))
      ,
      (
      SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG_2={1}>}SPREAD_w_LP_WEIGHT)/
      SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG_2={1}>}ENDING_BALANCE)
      ))

      The individual ratios at row level are correct but somehow at the totals levels I am getting totals only of the Else part of the expression. I want to get the expression total of entire data set. I am using pivot table. I cannot use aggr here since I have at least 15 dimensions and there visibility is conditional.

       

      Please help