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

# Help needed on previous year calculation

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.

Thanks.

• ###### Re: Help needed on previous year calculation

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

Dear Liron,

Yes. The user makes selection on the Effort Year.

Thanks

Silambarasan

• ###### Re: Help needed on previous year calculation

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.