9 Replies Latest reply: Aug 10, 2018 6:45 AM by Bharat Kishore RSS

    Formula help

    Bharat Kishore

      Hi All,


      I have period , flag and market as fields in a list box and I am trying to calculate average for YTD .


      The problem is when i select period and flag the average value is showing for that month. instead of showing for selected months and dividing by the selected month.


      For ex: I have month selected as march. the values for jan and feb and mar are 10,20,30. I need to get output as (10+20+30)/3 = 20


      But i here it is happening 30/3 =10.


      This is happening only if i select period and if i remove period and select any other combination  i am getting value correctly.


      Below image for your reference:




      Here the value is showing is 12 instead of 38. Can you please help me where i am doing wrong.


      Formula i have used is


      =If(SubStringCount('|' & Concat(distinct Dimension2, '|') & '|', '|Flag|'),Num(SUM({<Period_NR={'>=$(=MonthStart(vCurrYear_SelectedMnth1, 1-NUM(Month(vCurrYear_SelectedMnth1))))<=$(=vCurrYear_SelectedMnth1)'}



      Please let me know if you need anything.