Help needed on previous year calculation

[arasantorule]

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.

[lironbaram]

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

[arasantorule]

Dear Liron,

Yes. The user makes selection on the Effort Year.

Thanks

Silambarasan

[arasantorule]

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.