3 Replies Latest reply: Jul 2, 2017 11:50 PM by Silambarasan P RSS

    Help needed on previous year calculation

    Silambarasan P

      Dear Friends,

       

      I am trying to calculate previous year's results as below.

       

      if(GetSelectedCount(EffortYear)>0,

      num(((num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>}Efforts),'#,##0.00'))/ (num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>} aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={'$(=Max(EffortYear)-1)'}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))/Count(DISTINCT TOTAL {<EffortYear={'$(=Max(EffortYear)-1)'}>} MonthYear),'#,##0.00'))),'#,##0.00'),

      num(((num(sum({<EffortYear={'$(=Max(EffortYear)-1)'}>}Efforts),'#,##0.00'))/ (num(sum({<EffortYear={'$(=Year(Today())-1)'}>} aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={'$(=Year(Today())-1)'}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))/Count(DISTINCT TOTAL {<EffortYear={'$(=Max(EffortYear)-1)'}>} MonthYear),'#,##0.00'))),'#,##0.00'))

       

      It works fine when no years or more than one year selected.

       

      However, when we select only one year the above calculation gives no results. I have Tried all possibilities with no success.

       

      Could you please help to fix the issue?

       

      Thanks.

        • Re: Help needed on previous year calculation
          Liron Baram

          some questions

          1. does the user male selection on the field EffortYear?

          2.the cause might also be your if statement that doesn't have set analysis

            • Re: Help needed on previous year calculation
              Silambarasan P

              Dear Liron,

              Yes. The user makes selection on the Effort Year.

               

              Thanks

              Silambarasan

                • Re: Help needed on previous year calculation
                  Silambarasan P

                  Dear Friends,

                   

                  I have made changes to the expression as below.

                   

                  if(GetSelectedCount(EffortYear)>0,num(((num(sum({<EffortYear={"$(=Max(EffortYear)-1)"}>}Efforts),'#,##0.00'))/

                  (num(sum({<EffortYear={"$(=Max(EffortYear)-1)"}>} aggr(Count({<EffortYear={"$(=Max(EffortYear)-1)"}>} DISTINCT  if(Aggr( sum({<EffortYear={"$(=Max(EffortYear)-1)"}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))

                  /Count({<EffortYear={"$(=Max(EffortYear)-1)"}>}DISTINCT TOTAL {<EffortYear={"$(=Max(EffortYear)-1)"}>} MonthYear),'#,##0.00'))),'#,##0.00'),

                  num(((num(sum({<EffortYear={"$(=Year(Today())-1)"}>}Efforts),'#,##0.00'))/ (num(sum( aggr(Count(DISTINCT  if(Aggr( sum({<EffortYear={"$(=Year(Today())-1)"}>} Efforts),[Staff ID],MonthYear)>0, MonthYear)),[Staff ID],MonthYear))

                  /Count(DISTINCT TOTAL {<EffortYear={"$(=Year(Today())-1)"}>} MonthYear),'#,##0.00'))),'#,##0.00'))

                   

                  Its working fine now.

                   

                  Thanks

                  Silambarasan.