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:

       

      T.PNG

       

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

      >}IB),'#,##0.0%')/Month(Period_NR))

       

      Please let me know if you need anything.

       

      Thanks,

      Bharat